Insert rows for every combination of key columns values

Question

Abstract

Let us imagine a table big about 20 thousands rows. Of all the columns, three of them are key fields. For every combination of such key fields I need to enter a new row with a specific column set to 0, and copying every other cell's value.

Consider that every value (including the key fields) can be duplicated.

How can I do this in an automated way?

Example:

Current table

Here is an example of my current table, with the three key fields and its numeric field I need to create a new row and set it to 0.

FieldKey 1Key 2Key 3Numeric fieldCombination 1ABC45Combination 1ABC23Combination 2ACF57Combination 3DEF76### Desired output

This is the table I want:

FieldKey 1Key 2Key 3Numeric fieldCombination 1ABC45Combination 1ABC23Combination 1ABC0Combination 2ACF57Combination 2ACF0Combination 3DEF76Combination 3DEF0How can I do this in an automated way?

Answer 1

You can do this with a pair of macros. One "searching" macro to determine when you need to insert a new row and one "processing" macro to do the work.

Developing the processing macro is easy. Create a spreadsheet with your sample data. Sort it on the three key columns. Then record a macro and manually perform all the processing steps:

• Insert a new row at the desired location.
• Copy the existing row to the new one.
• Set the desired cell to 0

This macro will have hard-coded cell references which you will need to replace with references provided by the searching macro

While you will need to build the searching macro from scratch, the processing is not complicated.

• Sort the data on the three key columns
• Starting a row 2, loop through the rows.
  • If the three key cells do not match the cells in the previous row, invoke the processing macro for this row and then skip one row
  • Repeat for next row.
• Sort the data back to original order if necessary