How to duplicate rows with only one column has value?

Question

Hi all,

I have a table with four columns: the first column is an ID, and the next three columns contain values. I need to duplicate the rows for each ID based on the 3 value columns. For example, if only two columns have data, I need to duplicate it to two rows, with only one column has value.

The input is:

IDValue#1Value#2Value#3AAA1020 BBB 2030

The output is:

IDValue#1Value#2Value#3AAA10  AAA 20BBB 20BBB  30

Thanks ahead!!!

Carolyn · Accepted Answer

Give this a try! What I did:

* Transpose to get all the values in one column
* Filter to exclude nulls
* Key part - I used the Multi-Row Tool to create a new column called "Row ID" where I gave each value its own ID* Note - if you have 2024.2, you can just use the Record ID tool with Grouping by the ID field

* Cross Tab to put it back together
* Sort & Select to order and clean up

Step #1 - Transpose:

Step #3 - Multi-Row Formula:

Result:

binu_acs · Accepted Answer

@emmazyan another approach using tile tool