Rescue oddly shaped data – Battle between Formulas, VBA and Power Query
Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.
How do you reshape it to one column?
You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.
Formula Forensics 042: Reverse Text – A Formulaic Solution
Use the new Textjoin() function to reverse a string of characters suing a formulaic solution.
Formula Forensics No. 041 – Convert a Roman Numeral to a Number
Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.
Formula Forensics No. 039 – Find the Cell Address for a value (2D & 3D Reverse Lookup)
Find the Cell Address for a value from a 2D or 3D Range
(2D & 3D Reverse Lookup)
Formula Forensics No. 038 – Find Which Worksheet a Max or Min Value is located on
Learn how to find which worksheet a max or min value occurs on using this neat formula
Looking up when the data won’t co-operate (case study)
Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.
Recently I found myself in such a situation and learned something new.
Introducing … data that won’t play nice
Drum roll please. Here is a data-set that I got from somewhere.
The problem – build a lookup formula
And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.
But how?
3D Max Formula for Excel
We all know about the MAX formula. But do you know about 3D Max?
Sounds intriguing? Read on.
Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.
For one of the reports, you need to find out the maximum transactions by any customer across months.
But there is a twist in the story.
Your data is not in one sheet. It is in multiple sheets, one per month.