Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to take data from one table and rearrange columns into new ta | Excel Discussion (Misc queries) | |||
EXcel 2002: How to rearrange data table ? | Excel Discussion (Misc queries) | |||
rearrange data | Excel Discussion (Misc queries) | |||
How do i rearrange pie chart without re-arranging source table? | Charts and Charting in Excel | |||
Rearrange data columns in Pivot Table | Excel Discussion (Misc queries) |