The following isn’t meant to be a “how-to.” This is designed to quickly show how we got over a challenge faced by a client who didn’t think that there was a simple solution. Have a look. Maybe this will inspire you to think about ways that your own spreadsheets/data can be massaged more efficiently.

Challenge

Here is a situation where someone was given a massive amount of data that wasn’t immediately useful for what she was hired to do.

  • Client had 300 rows of data on one spreadsheet
  • Needed to insert 13 rows between each existing row
  • Later, populate each space with financial data from another spreadsheet

My job was to figure out how to insert the 13 rows without manually one-by-one inserting the rows.

Impact

  • 2 days of repetitive Copy/Insert and my client was less than half-way through the 300 rows
  • Expensive! My client was being paid more than $25/hour to do accounting, not COPY/INSERT
  • FRUSTRATION!

Solution

  1. Give the original 300 rows a unique identifier by numbering them such that each row has 13 spaces in between; i.e., 1, 15, 29, 43 …
  2. Generate a column starting with 1 and going down to 4201
  3. Use VLOOKUP to locate the data that’s correlated to 1, 15, 29, 43 …
  4. Where there is no correlative value (e.g. 7) the VLOOKUP will return #N/A
  5. Delete all occurrences  of #N/A
  6. COPY and PASTE VALUES to get rid of the underlying formulae
  7. CELEBRATE!!!!

Elapsed time 15 minutes from problem to strategy
1 hour for implementation, cleanup and validation

Result

  • The gnashing of teeth ended. No more COPY/INSERT @ $25/hr
  • Savings of at least $500
  • Savings of at least 20 hours of manpower
  • The project was free to move forward