Insert rows for every combination of key columns values

Ishtar Terra 40 Reputation points
2024-06-14T09:55:59.55+00:00

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?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,646 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,680 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Barry Schwarz 2,346 Reputation points
    2024-06-14T16:47:10.6366667+00:00

    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
    0 comments