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
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))
MATCH(B$6,$B$1:$E$1,0) instead of 1 while for 3 we can use MATCH(C$6,$B$1:$E$1,0)$ are anchors, so we taking the absolute ranges here, so when we drag the references dont change. Refer answers I have updated the formulas,