Reputation:

Flatten multiple columns into new rows

How can I turn a table with duplicated columns:

ID    Var     Var     Name     Name
01    0001    0002    Bill     Jim
02    0003    0004    Sam      Kyle

into a new table where each original column only appears once, and its values are split among new rows with duplicated independent variables.

ID    Var     Name
01    0001    Bill
01    0002    Jim
02    0003    Sam
02    0004    Kyle

Upvotes: 0

Answers (1)

Reputation: 27233

You may try in this way as well,

• Formula used in cell A7 for the ID's

=INDEX($A$2:$A$3,INT((ROW(A1)-1)/2)+1)

• Formula used in cell B7 for Var's

=INDEX($B$2:$E$3,MATCH($A7,$A$2:$A$3,0),MOD((ROW(A1)-1),2)+1)

• Formula used in cell C7 for Name's

=INDEX($B$2:$E$3,MATCH($A7,$A$2:$A$3,0),MOD((ROW(A1)-1),2)+3)

Edit,

• Formula used in cell A7

=INDEX($A$2:$A$3,INT((ROW(A1)-1)/MAX(COUNTIF($B$1:$I$1,$B$1:$I$1))+1))

• Formula used in cell B7

=INDEX($B$2:$I$3,MATCH($A7,$A$2:$A$3,0),
MOD((ROW(A1)-1),MAX(COUNTIF($B$1:$I$1,$B$1:$I$1)))+MATCH(B$6,$B$1:$I$1,0))

And Fill down & Fill Right !

Alternatively we can use this as well,

For ID in cell A7

=INDEX($A$2:$A$3,INT((ROW(A1)-1)/(COLUMNS($B$1:$I$1)/2)+1))

in cell B7

=INDEX($B$2:$I$3,MATCH($A7,$A$2:$A$3,0),
MOD((ROW(A1)-1),COLUMNS($B$1:$I$1)/2)+MATCH(B$6,$B$1:$I$1,0))

Upvotes: 0