How to duplicate rows with only one column has value?

[emmazyan]

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:

ID	Value#1	Value#2	Value#3
AAA	10	20
BBB		20	30

The output is:

ID	Value#1	Value#2	Value#3
AAA	10
AAA		20
BBB		20
BBB			30

Thanks ahead!!!

[Carolyn]

Give this a try! What I did:

1. Transpose to get all the values in one column
2. Filter to exclude nulls
3. Key part - I used the Multi-Row Tool to create a new column called "Row ID" where I gave each value its own ID
   1. Note - if you have 2024.2, you can just use the Record ID tool with Grouping by the ID field
4. Cross Tab to put it back together
5. Sort & Select to order and clean up

Step #1 - Transpose:

Step #3 - Multi-Row Formula:

Result: 

[binuacs]

@emmazyan another approach using tile tool

[Carolyn]

@binuacs - As I was working on mine, I said "I think the Tile Tool is going to be in someone else's answer" - I'm not great at it and need to spend some time figuring it out :)