Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table to new data sheet?
I have a large sheet of about 12,000 rows that I need to reformat to
add to an Access Data table. My Excel sheet has sales volume by month (for multiple IDs and customers) vertically. I would like to take this data and make the months columns so that my rows are only about 400 instead of 12,000. I can create a pivot table, but it groups the items where there are multiple products or IDs for one customer and therefore it doesn't work for my Access table. Is there a way to create a pivot table to re-sort my data in this way without making goupings? So instead of something like this: Customer_Name Customer_ID Product Jan_06 Feb_06 Customer 4 33335 Product 1 92 85 Product 2 55555 Product 1 0 143 54446 Product 1 66443 Product 1 0 0 557989 Product 1 It would look more like this: Customer_Name Customer_ID Product Jan_06 Feb_06 Customer 4 33335 Product 1 92 85 Customer 4 33335 Product 2 Customer 4 55555 Product 1 0 143 Customer 4 54446 Product 1 Customer 4 66443 Product 1 0 0 Customer 4 557989 Product 1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table to new data sheet?
It sounds like your data is already in the best format to store in
Access, with a field that indicates the month, and one field for the quantities. But, if you need to rearrange it, the row headings show once in a Pivot Table, and there's no setting you can change, to force them to repeat. To create a table with a heading on each row, you could copy the pivot table, paste it as values in another location, and fill in the blanks, using the technique shown he http://www.contextures.com/xlDataEntry02.html A-Lo wrote: I have a large sheet of about 12,000 rows that I need to reformat to add to an Access Data table. My Excel sheet has sales volume by month (for multiple IDs and customers) vertically. I would like to take this data and make the months columns so that my rows are only about 400 instead of 12,000. I can create a pivot table, but it groups the items where there are multiple products or IDs for one customer and therefore it doesn't work for my Access table. Is there a way to create a pivot table to re-sort my data in this way without making goupings? So instead of something like this: Customer_Name Customer_ID Product Jan_06 Feb_06 Customer 4 33335 Product 1 92 85 Product 2 55555 Product 1 0 143 54446 Product 1 66443 Product 1 0 0 557989 Product 1 It would look more like this: Customer_Name Customer_ID Product Jan_06 Feb_06 Customer 4 33335 Product 1 92 85 Customer 4 33335 Product 2 Customer 4 55555 Product 1 0 143 Customer 4 54446 Product 1 Customer 4 66443 Product 1 0 0 Customer 4 557989 Product 1 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table to new data sheet?
See your previous post.
A-Lo wrote: I have a large sheet of about 12,000 rows that I need to reformat to add to an Access Data table. My Excel sheet has sales volume by month (for multiple IDs and customers) vertically. I would like to take this data and make the months columns so that my rows are only about 400 instead of 12,000. I can create a pivot table, but it groups the items where there are multiple products or IDs for one customer and therefore it doesn't work for my Access table. Is there a way to create a pivot table to re-sort my data in this way without making goupings? So instead of something like this: Customer_Name Customer_ID Product Jan_06 Feb_06 Customer 4 33335 Product 1 92 85 Product 2 55555 Product 1 0 143 54446 Product 1 66443 Product 1 0 0 557989 Product 1 It would look more like this: Customer_Name Customer_ID Product Jan_06 Feb_06 Customer 4 33335 Product 1 92 85 Customer 4 33335 Product 2 Customer 4 55555 Product 1 0 143 Customer 4 54446 Product 1 Customer 4 66443 Product 1 0 0 Customer 4 557989 Product 1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Data Sheet? | Excel Discussion (Misc queries) | |||
Pivot Table - Data Sheet? | Excel Discussion (Misc queries) | |||
How do I sort pivot table data outside a pivot table | Excel Worksheet Functions | |||
linking pivot table data to a cell in other sheet | Excel Worksheet Functions | |||
Presever formatting on Data sheet in Pivot table | Excel Discussion (Misc queries) |