Transpose array with multiple columns per 1 row

**alxw** · 06-27-2013, 02:03 PM

Hi,
I'm looking to solve a problem in excel. I have an array with several values (raws) per document number and I need to transpose this information as only one row with multiple columns. The example is attached. The simple paste special / transpose action doesn't work for this purpose. Any one can help please, Thank you.

BASE INFORMATION
Doc Number Position Product Qty UoM Source Father
1111 10 123 1 PC 55
1111 11 112 2 PC 55 10
1111 12 113 3 PC 55 10
2222 20 234 4 BAG 55
2222 21 221 5 BAG 55 20
2222 22 222 6 BAG 55 20
3333 30 345 7 KG 55
NNN 40 456 8 PC 55

FINAL TEMPLATE REQUIRED
Doc Number Position Product Qty UoM Source Father Position Product Qty UoM Source Father Position Product Qty UoM Source Father
1111 10 123 1 PC 55 11 112 2 PC 55 10 12 113 3 PC 55 10
2222 21 221 5 BAG 55 20 22 222 6 BAG 55 20
3333 30 345 7 KG 55
NNN 40 456 8 PC 55

**AZ-XL** · 06-27-2013, 03:31 PM

Hi.
I used there helper column in data table. After that use paste this array formula to b14 cell:
=IFERROR(INDEX($B$3:$G$10,LARGE(IF(($A$3:$A$10=$A21)*($H$3:$H$10=COUNTIF($B$20:B$20,B$20)),ROW($A$3:$A$10)-2,""),1),MATCH(B$20,$B$2:$G$2,0)),"") CSE

Uploading file.here. Didnt touch your outcime table. Created my own below it