The Wildest Excel Spreadsheet Ever: HLOOKUP and the Importance of Building a Model

NOTE: This video isn’t about the details. It’s the thought process. So, please don’t worry if you don’t catch everything.

Download here: http://datascopic.net/messiest

Subscriber, eCabinets Tips and Tricks asked about the most bizarre spreadsheet I’ve ever faced, and what I did to untangle it.

‘Bizarre’ can be measured a lot of ways. I chose this example because it almost beat me. I almost threw up my hands and accepted defeat. It was too big to even try to handle manually.

I’ve faced spreadsheets that had far more complexity, but I could see the solution and it was just a matter of plowing through. But this one … it took a week to find a way to even get started.

It was a spreadsheet that had 2000 rows and over 600 columns. The data in the columns were all out of order, and needed to be moved in groups of 15. My job was to get them all in order.

Two things made a solution possible:
1. HLOOKUP (this is the only time I’ve ever used HLOOKUP. Ever.)
2. Making a small, simple model of the problem that could then be scaled out to the real data.

#2 is what I really want to stress: when a task is overwhelming, slow down. Make a small model. Clear away the noise and just focus on developing a solution.

eCabinets Tip and Tricks
https://www.youtube.com/user/ecabinetstips
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2