Spreadsheet Manipulation: Inserting Rows in a Pattern

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