rearrange the data with a new table
 

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

rearrange the data with a new table
 

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

"KS" wrote in message
...
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

rearrange the data with a new table
 

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


"Fred Smith" wrote:

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

"KS" wrote in message
...
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

rearrange the data with a new table
 

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.


"KS" wrote in message
...
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


"Fred Smith" wrote:

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

"KS" wrote in message
...
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

rearrange the data with a new table
 

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

rearrange the data with a new table
 

Max, you are awesome.
Thanks a lot.

"Max" wrote:

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

rearrange the data with a new table
 

Welcome, glad it worked out fine for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"KS" wrote in message
...
Max, you are awesome.
Thanks a lot.