Transpose every 4 column(6 x4 columns) into different row for each cust-MAC EXCEL 2011

[mei xia]

Hi everyone

I need help to copy 6 set of 4 column to a new row for each customer

Before Result
CUST|Q1|P1|D1|A1|Q2|P2|D2|A2|Q3|P3|D3|A3|Q4|P4|D4|A4|Q5|P5|D5|A5|Q6|P6|D6|A6|
1234|1 |A |AA|1 |1 |B |BB|2 |1 |C |CC|3 |1 |D |DD|4 | | | | | | | | |
1235|1 |A |AA|1 |1 |B |BB|2 | | | | | | | | | | | | | | | | |
1236|1 |A |AA|1 |1 |B |BB|2 |1 |C |CC|3 |1 |D |DD|4 |1 |E |EE|5 | | | | |
1237|1 |A |AA|1 |1 |B |BB|2 | | | | | | | | | | | | | | | | |

Transpose Result
1234|Q1|P1|D1|A1|
1234|Q2|P2|D2|A2|
1234|Q3|P3|D3|A3|
1234|Q4|P4|D4|A4|
1234|Q5|P5|D5|A5|
1234|Q6|P6|D6|A6|
1235|Q1|P1|D1|A1|
1235|Q2|P2|D2|A2|
1235|Q3|P3|D3|A3|
1235|Q4|P4|D4|A4|
1235|Q5|P5|D5|A5|
1235|Q6|P6|D6|A6|
1236|Q1|P1|D1|A1|
1236|Q2|P2|D2|A2|
1236|Q3|P3|D3|A3|
1236|Q4|P4|D4|A4|
1236|Q5|P5|D5|A5|
1236|Q6|P6|D6|A6|
1237|Q1|P1|D1|A1|
1237|Q2|P2|D2|A2|
1237|Q3|P3|D3|A3|
1237|Q4|P4|D4|A4|
1237|Q5|P5|D5|A5|
1237|Q6|P6|D6|A6|
Data Result as follow
1234|1 |A |AA|1 |
1234|1 |B |BB|2 |
1234|1 |C |CC|3 |
1234|1 |D |DD|4 |
1234| | | | |
1234| | | | |
1235|1 |A |AA|1 |
1235|1 |B |BB|2 |
1235| | | | |
1235| | | | |
1235| | | | |
1235| | | | |
1236|1 |A |AA|1 |
1236|1 |B |BB|2 |
1236|1 |C |CC|3 |
1236|1 |D |DD|4 |
1236|1 |E |EE|5 |
1236| | | | |
1237|1 |A |AA|1 |
1237|1 |B |BB|2 |
1237| | | | |
1237| | | | |
1237| | | | |
1237| | | | |

Please help to suggest the solution to get the result above.

Thanks

MEI XIA

 

[bosco_yip]

Try..........

1] In A14, formula copy down :

=IFERROR(INDEX(A$4:A$7,INT((ROWS($1:1)-1)/6)+1),"")

2] In B14, formula copy across to E14 and all copy down :

=IF($A14="","",INDEX($B$4:$Y$7,MATCH($A14,$A$4:$A$7,0),MATCH(B$13&COUNTIF($A$14:$A14,$A14),$B$3:$Y$3,0)))

Regards
Bosco

 

[mei xia]

Hi Brosco

THANK A LOT!!

it work out VERY WELL!

YEAH!

mei xia