Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Kerry
 
Posts: n/a
Default Pivot table column duplicates

I have a simple pivot table referencing three columns of data: store name,
item number, quantity sold. When I convert this to a pivot table, showing
store name and item number as X and Y axis, and quantity sold as the data, I
get duplications for the item numbers. The caveat is that it doesn't
duplicate exactly, it looks like this:
Item #1 Item #1 Item #2 Item #2 Item #3 Item #3
Store A 55 66 77
Store B 44 88 99
Store C 33 22
33

What can I do to get all my data to show just one column for each item
number? Since I only have three columns of data that I am referencing, I
don't understand why the Pivot table is splitting this data.
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

If the Item numbers are entered differently in the source table, they'll
show up as different items in the pivot table. Perhaps some of the item
numbers have spaces at the end, and others don't.

To limit the entries, you could use a data validation list in the source
data table. There are instructions in Excel's help, and he

http://www.contextures.com/xlDataVal01.html

Kerry wrote:
I have a simple pivot table referencing three columns of data: store name,
item number, quantity sold. When I convert this to a pivot table, showing
store name and item number as X and Y axis, and quantity sold as the data, I
get duplications for the item numbers. The caveat is that it doesn't
duplicate exactly, it looks like this:
Item #1 Item #1 Item #2 Item #2 Item #3 Item #3
Store A 55 66 77
Store B 44 88 99
Store C 33 22
33

What can I do to get all my data to show just one column for each item
number? Since I only have three columns of data that I am referencing, I
don't understand why the Pivot table is splitting this data.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #3   Report Post  
Kerry
 
Posts: n/a
Default

This did the trick! We actually used the text to columns function to limit
the item number to five digits, which got rid of the spaces. Thanks for the
assistance!

"Debra Dalgleish" wrote:

If the Item numbers are entered differently in the source table, they'll
show up as different items in the pivot table. Perhaps some of the item
numbers have spaces at the end, and others don't.

To limit the entries, you could use a data validation list in the source
data table. There are instructions in Excel's help, and he

http://www.contextures.com/xlDataVal01.html

Kerry wrote:
I have a simple pivot table referencing three columns of data: store name,
item number, quantity sold. When I convert this to a pivot table, showing
store name and item number as X and Y axis, and quantity sold as the data, I
get duplications for the item numbers. The caveat is that it doesn't
duplicate exactly, it looks like this:
Item #1 Item #1 Item #2 Item #2 Item #3 Item #3
Store A 55 66 77
Store B 44 88 99
Store C 33 22
33

What can I do to get all my data to show just one column for each item
number? Since I only have three columns of data that I am referencing, I
don't understand why the Pivot table is splitting this data.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #4   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

You're welcome! Thanks for letting me know how you fixed it.

Kerry wrote:
This did the trick! We actually used the text to columns function to limit
the item number to five digits, which got rid of the spaces. Thanks for the
assistance!

"Debra Dalgleish" wrote:


If the Item numbers are entered differently in the source table, they'll
show up as different items in the pivot table. Perhaps some of the item
numbers have spaces at the end, and others don't.

To limit the entries, you could use a data validation list in the source
data table. There are instructions in Excel's help, and he

http://www.contextures.com/xlDataVal01.html

Kerry wrote:

I have a simple pivot table referencing three columns of data: store name,
item number, quantity sold. When I convert this to a pivot table, showing
store name and item number as X and Y axis, and quantity sold as the data, I
get duplications for the item numbers. The caveat is that it doesn't
duplicate exactly, it looks like this:
Item #1 Item #1 Item #2 Item #2 Item #3 Item #3
Store A 55 66 77
Store B 44 88 99
Store C 33 22
33

What can I do to get all my data to show just one column for each item
number? Since I only have three columns of data that I am referencing, I
don't understand why the Pivot table is splitting this data.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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
updating pivot table to include additional rows Ellen Excel Discussion (Misc queries) 8 July 15th 08 01:33 PM
How do I keep with column widths of a pivot table when I refresh Kaz Excel Worksheet Functions 1 September 7th 05 10:23 PM
Pivot table keeps dupping to another linked pivot table Angus Excel Discussion (Misc queries) 0 August 8th 05 07:48 AM
Pivot -- want to use Max and Sum in same table Dave Excel Worksheet Functions 1 July 13th 05 04:37 AM
Change Data In Pivot Table John Calder New Users to Excel 1 July 7th 05 10:41 PM


All times are GMT +1. The time now is 10:33 AM.

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"