Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating pivot table to include additional rows | Excel Discussion (Misc queries) | |||
How do I keep with column widths of a pivot table when I refresh | Excel Worksheet Functions | |||
Pivot table keeps dupping to another linked pivot table | Excel Discussion (Misc queries) | |||
Pivot -- want to use Max and Sum in same table | Excel Worksheet Functions | |||
Change Data In Pivot Table | New Users to Excel |