Transposing Several Rows Into Multiple Columns

[12learnexcel]

Hi everyone. I've been searching for a thread about this query but all I found refer to Multiple Rows to Single Columns and the like.
I need help in transposing Several Rows into Multiple Columns and I would appreciate if it's in a formula format and not VBA.
The table looks like this:

[TABLE="width: 555"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Detail[/TD]
[TD="colspan: 5"]Sale[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]Date of Sale[/TD]
[TD]A1[/TD]
[TD]A2[/TD]
[TD]A3[/TD]
[TD]A4[/TD]
[TD]A5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[TD]B3[/TD]
[TD]B4[/TD]
[TD]B5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Quantity[/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C3[/TD]
[TD]C4[/TD]
[TD]C5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Price / Pc[/TD]
[TD]D1[/TD]
[TD]D2[/TD]
[TD]D3[/TD]
[TD]D4[/TD]
[TD]D5[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]Date of Sale[/TD]
[TD]A6[/TD]
[TD]A7[/TD]
[TD]A8[/TD]
[TD]A9[/TD]
[TD]A10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD]B6[/TD]
[TD]B7[/TD]
[TD]B8[/TD]
[TD]B9[/TD]
[TD]B10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Quantity[/TD]
[TD]C6[/TD]
[TD]C7[/TD]
[TD]C8[/TD]
[TD]C9[/TD]
[TD]C10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Price / Pc[/TD]
[TD]D6[/TD]
[TD]D7[/TD]
[TD]D8[/TD]
[TD]D9[/TD]
[TD]D10[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]Date of Sale[/TD]
[TD]A11[/TD]
[TD]A12[/TD]
[TD]A13[/TD]
[TD]A14[/TD]
[TD]A15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD]B11[/TD]
[TD]B12[/TD]
[TD]B13[/TD]
[TD]B14[/TD]
[TD]B15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Quantity[/TD]
[TD]C11[/TD]
[TD]C12[/TD]
[TD]C13[/TD]
[TD]C14[/TD]
[TD]C15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Price / Pc[/TD]
[TD]D11[/TD]
[TD]D12[/TD]
[TD]D13[/TD]
[TD]D14[/TD]
[TD]D15[/TD]
[/TR]
</tbody>[/TABLE]

Please note that the Sale number extends up to 20 and the Products are up to 200.

I need it to look like this on another worksheet:

[TABLE="width: 501"]
<tbody>[TR]
[TD="colspan: 3"]Consolidated Sales List[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product[/TD]
[TD]Date of Sale[/TD]
[TD]Client Name[/TD]
[TD]Quantity[/TD]
[TD]Price / Pc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]C3[/TD]
[TD]D3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]A4[/TD]
[TD]B4[/TD]
[TD]C4[/TD]
[TD]D4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]A5[/TD]
[TD]B5[/TD]
[TD]C5[/TD]
[TD]D5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 2[/TD]
[TD]A6[/TD]
[TD]B6[/TD]
[TD]C6[/TD]
[TD]D6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 2[/TD]
[TD]A7[/TD]
[TD]B7[/TD]
[TD]C7[/TD]
[TD]D7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 2[/TD]
[TD]A8[/TD]
[TD]B8[/TD]
[TD]C8[/TD]
[TD]D8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 2[/TD]
[TD]A9[/TD]
[TD]B9[/TD]
[TD]C9[/TD]
[TD]D9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 2[/TD]
[TD]A10[/TD]
[TD]B10[/TD]
[TD]C10[/TD]
[TD]D10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 3[/TD]
[TD]A11[/TD]
[TD]B11[/TD]
[TD]C11[/TD]
[TD]D11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 3[/TD]
[TD]A12[/TD]
[TD]B12[/TD]
[TD]C12[/TD]
[TD]D12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 3[/TD]
[TD]A13[/TD]
[TD]B13[/TD]
[TD]C13[/TD]
[TD]D13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 3[/TD]
[TD]A14[/TD]
[TD]B14[/TD]
[TD]C14[/TD]
[TD]D14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product 3[/TD]
[TD]A15[/TD]
[TD]B15[/TD]
[TD]C15[/TD]
[TD]D15[/TD]
[/TR]
</tbody>[/TABLE]


I've been trying to research about this and work it out myself but my deadline is fast approaching. Thanks everyone in advance for your help.

 

[smkyle]

Step 1 in same sheet. Copy formula down and across as far as you need to

Sheet1

*	A	B	C	D	E	F	G	H
Product	*	*	*	*	*	*	*
*	*	*
Product 1	Date of Sale	A1	A2	A3	A4	A5	A6
*	Client Name	B1	B2	B3	B4	B5	*
*	Quantity	C1	C2	C3	C4	C5	*
*	Price / Pc	D1	D2	D3	D4	D5	*
Product 2	Date of Sale	A6	A7	A8	A9	A10	*
*	Client Name	B6	B7	B8	B9	B10	*
*	Quantity	C6	C7	C8	C9	C10	*
*	Price / Pc	D6	D7	D8	D9	D10	*
Product 3	Date of Sale	A11	A12	A13	A14	A15	*
*	Client Name	B11	B12	B13	B14	B15	*
*	Quantity	C11	C12	C13	C14	C15	*
*	Price / Pc	D11	D12	D13	D14	D15	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H3 =OFFSET($C3,4*INT((COLUMN()-3)/5),MOD(COLUMN()-3,5)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[smkyle]

Step 2 in new sheet. Copy Formula Down, Copy second formula down and across as far as you need to. You will have to tweak these formulas to adjust for number of products, etc.

Sheet2

*	A	B	C	D	E
Product	Date of Sale	Client Name	Quantity	Price / PC
Product 1	A1	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:63px;"><col style="width:78px;"><col style="width:78px;"><col style="width:56px;"><col style="width:67px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 ="Product "&INT((ROW()+3)/5) B2 =OFFSET(Sheet1!$C$3,COLUMN()-2,ROW()-2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[12learnexcel]

hi smkyle,

thank you so much for the formula. i'll try this out.

 

[12learnexcel]

hi smkyle,

the formulas are awesome. thank you.

in sheet 1, everything looks great. i have no problem with the formula. thank you.

but in sheet 2, it was also ok except that i need all the 20 sales records of all the 200 products to be consolidated into all 5 columns only (Product, Date of Sale, Client Name, Quantity and Price/Pc) such that under these 5 headers there will be 20 rows of Product1's followed by 20 rows of Product2's and so on up to 20 rows of Product200's. what the formula did was,it consolidated the 20 sales records of each product into 20 different list sets (which i believe was my mistake since i mentioned that i needed it to be transposed, i apologize, i actually needed them to be consolidated). i tried tweaking the formula myself but it seemed i need more training.

anyway, help please. and thank you so much in advance.

 

[smkyle]

could you use Excel Jeanie to show me what you have? I think you may want to either use a pivot table or to do something like this:

Excel Workbook
	A	B	C	D	E
1	Product 1	1		Product 1	15
2	Product 1	2		Product 2	10
3	Product 1	3
4	Product 1	4
5	Product 1	5
6	Product 2	1
7	Product 2	2
8	Product 2	3
9	Product 2	4
Sheet1

Personally I would go with a pivot table because they are flexible and easy to change to suit your style preferences. Totally up to you though

 

[12learnexcel]

hi smkyle,

thank you very much for your help. i rearranged my table to achieve the results that i wanted and the formula you suggested is working fine now.

thanks again.