This stood as the only time in 12 years that I almost accepted defeat. With 70% of the work done, the last 30% was excruciating.
Summary
The client was conducting a Data Transfer from one database to another. They successfully downloaded the data from the old database and into an Excel spreadsheet. Unfortunately, the data wasn’t lined up and ready to be imported into the new database.
- We had over 800 columns and 11,000 rows of customers and purchases
- 60 products and product details in columns
- Column headers were correct, the content was misaligned
- When one product was moved into place, it mean moving 11 cells: the product name and its 10 associated details
Challenges
Click here for a simple representation of the challenge. To capture the concepts and protect the client’s data, I created something simple. This is the miniature model that I built to test possible solutions.
Notice Jake’s row In the BEFORE section. His Peppers are where his Cinnamon should be. His Apple is where Cherries should be. When we move the Peppers into place, we also need to move the Weight and Cost.
Yes! We have a slide puzzle.
Challenge Details
- Minimize the human activity of copy/paste, drag/drop
- With so much data it woud be easy to miss a cell, paste into the wrong area … and not know something was wrong. Thus, even a decision to manually move everything would be a decision to accept a lot of errors.
- Ensure that the product and associated cells move together
- Strategize how to move the pieces. Let’s think about it this way using the alphabet:
- X has to move before Y can move, but C has to move out of Y’s space. Oh! K is in C’s spot.
- E, R and J are in the right places
- 26 spots, 20 are filled, and only 3 are correct
Solution
(Warning: This section contains Excel p0rn)
- First, let’s rebuild the document in pieces on a Re-build Sheet and assess how big the problem is. Most people had just 1 or 2 purchases.
- Look at the single purchases
- Move the ones that are correct onto the Re-build Sheet
- Correct the ones that have their single item out of place and move them to the Re-build Sheet. This helps clear the space to work with only the items that still need work.
- Do the same with the 2-item rows. Get them out of the way.
70% is done and 30% left.
Many rows just had three products to shuffle. Some had more than 40 that had to move. This is where it gets juicy! This is also where I felt panicky because this was a client’s data, I was on the clock and had to deliver accuracy because their Data Transfer had to be clean. I couldn’t take 10 steps and then realize that step #2 was wrong. There would be no backing up 8 steps. No! That means start all over again.
So I thought about it.
(This is where Excel-sensitive guests may want to close their eyes.)
It took several days to come up with this solution and 1 hour to execute it. I decided to rebuild the remainders underneath the initial dataset, just like you see in the image. The rebuild starts at row 17.
- HLOOKUP is used to look in Jake’s row and bring back Peppers IF he purchased peppers.
- Look at Guy’s row, he has no Peppers, no Cinnamon or Sugar, either.
- An Addy column is added where I used INDIRECT and CELL (address, ) to say where an item is after it’s found.
- With the cell’s address, OFFSET is used to retrieve the cells next to the address; i.e., “go to E3 and retrieve the contents of cells F3 and G3.”
- Lots of use of Absolute and Relative Cell References, then drag the formulae down and across.
The client was extremely happy with the result of getting 8.8 Million cells all lined up to successfully migrate their data. They were back in motion!
photo credit: lida skoteina via photo pin cc
