Is there an excel function to transform table A to table B?

One formulas play to deliver the desired transformation ..

Assuming Table A is in Sheet1's A1:D7
In another sheet,
In A2:
=OFFSET(Sheet1!$B$2,MOD(ROWS($1:1)-1,3),INT((ROWS($1:1)-1)/3))

In B2:
=IF(AND(ROWS($1:1)<=3*COLUMNS($A:A),3*COLUMNS($A:A )-2<=ROWS($1:1)),OFFSET(Sheet1!B$5,MOD(ROWS($1:1)-1,3),),"")
Copy B2 to D2. Select A2:D2, copy down to D10
Returns exactly what you seek for Table B

Click YES below to celebrate ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"KS" wrote:
The table is 30 x 60.
Table A shows the sales amount for each product with customers and sales
amount in the same column. And I want a new table to show the sales amount by
customers (by row) with detail on each product (by column). (ie. Table B).

I will then sort Table B by customers name, and find the subtotal sales
amount for each customer.

I need to update the data monthly.
Is there an excel function to transform table A to table B?

Thanks

Table A
Product 1 Product 2 Product 3
customer name A D E
customer name B A F
customer name C E C
sales amt 1 12 4 55
sales amt 2 33 422 431
sales amt 3 22 45 45

Table B
customer name Product 1 Product 2 Product 3
A 12
B 33
C 22
D 4
A 422
E 45
E 55
F 431
C 45