Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table - Data Sheet? [email protected] Excel Discussion (Misc queries) 3 March 6th 07 02:07 PM
Pivot Table - Data Sheet? [email protected] Excel Discussion (Misc queries) 0 March 5th 07 06:29 PM
How do I sort pivot table data outside a pivot table Michael Excel Worksheet Functions 1 January 4th 07 03:45 PM
linking pivot table data to a cell in other sheet sudip Excel Worksheet Functions 3 September 13th 06 02:34 PM
Presever formatting on Data sheet in Pivot table RMF Excel Discussion (Misc queries) 1 April 6th 06 01:46 AM


All times are GMT +1. The time now is 10:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"