rearrange the data with a new table

  • Thread starter Thread starter KS
  • Start date Start date
K

KS

I would like to transform my existing table A to table B.
Therefore, I can sort table B by customers for the subtotal.
Thanks. KS

Table A
Name
A D E
B A F
C E C
12 4 55
33 422 431
22 45 45
Table B
name amount
A 12
B 33
C 22
D 4
A 422
E 45
E 55
F 431
C 45
 
We need more information.

For Table A, how many cells are involved? For the names, is there 1, 3 or 9?
Same with the amounts. If 1 or 3, what separates the names from each other?

Do you need this as a one-time effort, or do you need a macro so you can
repeat the operation many times.

Regards,
Fred
 
Dear Fred,

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
 
Your layout still isn't very clear, and now you've added products to the
mix, which is even more confusing. I still have no idea how you associate a
sale amount with a product with a customer. So why don't you try telling us
that. Take CustomerA, Product1. How do you tell what the sales are for this
combination?

To answer your question, there isn't any Excel function which will transform
Table A into Table B.

Regards,
Fred.
 
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
 
Max, you are awesome.
Thanks a lot.

Max said:
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
Click to expand...
 
Back
Top