Hi everyone,
Long time no see!
It seems the busier one is, the more work gets done. I’ve published a nice article today, in which I explain how I created a Wheel Of Fortune made entirely in Excel some years ago. This is what it looks like:
Enjoy!
Jan Karel Pieterse
Hi there!
Now that summer has arrived I’ve planned a summer sale. From July 1st up to July 10th I offer a 25% discount on both products I sell:
RefTreeAnalyser
and
The Excel File Remediation Utility
To get your discount, just go through the purchasing process and enter this coupon code to redeem your discount:
JKPADS-Summer2015
Regards and have a great summer!
Jan Karel Pieterse
From the Office blog: Office 2016 Public Preview Now Available
Intuitive data connecting and shaping capabilities. With integrated Power Query, use Excel as your personal analysis workspace by connecting to and viewing all the data around you. Take advantage of a broad range of data sources, including tables from websites, corporate data like SAP Business Objects, unstructured sources like Hadoop, and services like Salesforce. After bringing all your data together in one place, quickly shape and combine to fit your unique business needs and get to analysis in seconds
Go here for instructions on how to get the preview.
I use Oracle Virtualbox to install previews. So far, the 2016 preview has been remarkably stable, but I’m not one for taking chances.
Hi there. For the last few months I’ve been working on my main add-in, AET Excel Utilities.
Update
So far I’m in the process of setting up some partners, had a translation offer, and downloads are happening as I write this. Thanks very much to everybody for your help. Don’t be shy if you are interested!
I first started working on it in 2005, as a hobby, and a way to learn VBA. Over time it’s grown from having a handful of very simple tools, to what it is now – well over a hundred utilities (more like over two hundred), and some of them quite complex, even if I do say so myself. Useful? I like to think so. Not a day goes by that I don’t use it, and I can honestly say it saves me lots of time.
But there’s a problem. Even though I like these utilities, I’m not very good at selling myself, letting alone anything I’ve made. And in the world of Excel, most folk have either never heard of me or think I’ve retired if they have. That’s been fine until now, with me plugging away in a corner, tinkering away, but it’s always bothered me that my tools could be so much more.
So, I’d ask all of you for some help. I’m making the tools shareware. And I’m looking for people to help sell them. Do you have a site? If so, are you willing to become a partner or an affiliate? Like I say, I’m not great at sales so any assistance would be appreciated. Translations? Great! Let’s talk about a percentage. I guess the main thing is making people aware of them. Apart from making a bit of pocket money, serious interest will give me incentive to improve them and maybe even try to give my site a bit of an overhaul. (Please contact me using aengwirda [at] gmail.com if you are interested).
Here’s a few screenshots to whet your interest. (Well maybe more than a few…). Look to the left, the AET UTILITIES tab shares both my main utilities and free add-ins (which you can download here).
Worksheet Tools
Rows And Columns
Formula Tools
Deletion Tools
Object Tools
Export Tools
Text Tools
Number Tools
Time And Date
Chart Tools
Path And Folder
Workbook Tools
Developer Tools
Fun And Games
Other Utilities
Cell Menu
Row Menu
Column Menu
Sheet Menu
Here’s the download page link. On the same webpage, you can also download a copy of the Help files for more details on the individual tools, plus the password to see how the code works.
In addition to adding more tools over the next few weeks, I’ll be working on my free utilities too. More details on them, and also some new code samples, that I’m looking forward to posting about in the near future.
It’s fun to argue about whether VLOOKUP or INDEX/MATCH is better, but to me that’s missing the point: they are both bad.
So I decided to design and build some better ones.
Here are some of the more-frequently mentioned VLOOKUP INDEX/MATCH problems
MEMLOOKUP ( Lookup_Value, Lookup_Array, Result_Col, Sort_Type, MemType_Name, Vertical_Horizontal )
The syntax is designed to make it easy to convert a VLOOKUP to MEMLOOKUP, but there are differences!
It’s always tempting to cram in more function (scope creep is universal), but if the result is too many parameters then it’s a mistake. So instead there is a whole family of these lookup functions that build on the MEMLOOKUP/MEMMATCH technology to provide the ultimate in flexibility and power whilst remaining efficient.
These functions are included in the 90 or so additional Excel functions built into FastExcel V3.
You can download the trial version from here.
I would be delighted to tell the Excel team how I built these functions and the algorithms they use.
By the way they are written as C++ multi-threaded functions in an XLL addin for maximum performance.
Hi everyone!
Every quarter Microsoft announces who are the lucky ones to receive their Most Valuable Professional Award. An MVP award lasts a year, so for a quarter of the MVPs, October 1st is an important day.
I got re-awarded!
And to celebrate that I am offering a 3 day 50 percent discount on my Formula auditing tool: RefTreeAnalyser
From October 8, 2014 to October 10, 2104 you receive 50 % off of the list price when you enter this coupon code: MVP2014
Head over to my website and download the tool, you can try it for free!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
When Doug posted about Data Comparison Tricks, I saw Dick tell him to “pimp his site” in the comments.
Having a vivid imagination, this is what went through my head.
Anyway, that’s what I’ll now proceed to do. (Having obtained Dick’s permission first of course!)
Here’s some stuff I’ve been working on recently.
A multi-field Find and Select/Replace tool.
Although a bit old, (like me), some of the code came from this.
Here’s the old post about it from back in 2009. (From my former blog, which I’ll also pimp!)
An alternative Status Bar that recognizes numbers even if the format is text. Woohoo!
And some games. (For the kids, but you can play too)
That’s enough pimpin’ for now. (I’m making new stuff as I write this) See you next time?
I’ve made an Excel template for filling out T accounts. For you non-accounting folks, a T account is a method to trace accounting transactions through the accounts on the general ledger. Hey, wake up! This post isn’t finished yet!
I’ve made a T account template at least three times in my Excel career. They never work out. They never make it easier than pencil-to-paper or marker-to-whiteboard. It doesn’t seem to stop me from trying, though, and now I’m making my latest attempt available to you. First, let’s look at this huge image (click to embiggen).
The top left section (C2:F11) is where you write a brief description for up to 10 transactions. Column C is the transaction number and can’t be edited. Column D is where you type the brief description. Column E is overflow for column D because I hate merged cells. Column F is the general ledger period. You can put them all in the same GL Period if it’s not applicable to your transactions. You don’t have to use this section. That is, you can leave off the descriptions and the periods, but good luck trying to figure out what you were thinking.
In this example, the transactions are listed in the order they will happen over time. This series of transactions represent a sale where we get a kickback from the vendor and we’re passing that savings on to the customer. First, we buy the inventory from the vendor. Next, we ship it to the our customer. At that point we need to make our costs correct by accruing the rebate we’re entitled to from the vendor. We pay the vendor, get paid by the customer, and finally get our rebate from the Vendor.
The main section of the workbook is a 3×3 grid of T accounts. This is where the magic happens. The top left cell of each T account is a financials statement classification number. It’s a data validation dropdown containing the numbers 1-7.
That financial statement classification number is replicated down the hidden column B (and H and N) for formula purposes.
To the right of the financial statement classification number is the (merged) cell where you type the account name. The account name is only for your reference and has no bearing on any formulas in the model.
The lighter green section down the left side of the T account is a series of data validation dropdowns that allow you to select the transaction number. The main white area of the T account that’s divided vertically is the area where you record your debits and credits. At the bottom of the T account, the debits and credits are summed up and the net debit or credit is displayed.
There are two other areas to the right of the T accounts grid. The top area shows the effects on the major sections of your balance sheet and income statement. It expands to the right for however many periods you have. The financial statement classification number at the top left of the T account determines where your transaction ends up in this area. And, obviously, the period you identify in the transactions section determine the column.
The bottom area is a list of the 10 transactions and the net debit or credit by transaction. If any of these are not zero, you’re missing a piece of the transaction.
Please leave your thoughts and suggestions in the comments.
You can download TAccounts.zip