Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 2nd 06, 05:40 PM posted to microsoft.public.excel.misc
roadkill
 
Posts: n/a
Default Eliminate Duplicates in Pivot Table

I have a large spreadsheet with, among other columns, a column for PO number
and another for item number. I need to determine the number of unique PO's
for each item (just a count/quantity). If an item shows up twice on the same
PO, it only gets counted once. I thought a pivot table might do the trick
but don't see a setting for eliminating duplicates. Is there one? If not,
what's a good way to get the information needed (i.e. 2 columns - one with
the item number and the other with the number of unique PO's that contain
that item number)?

Any help would be appreciated.

Thanks,
Will

  #2   Report Post  
Old February 2nd 06, 09:00 PM posted to microsoft.public.excel.misc
gjcase
 
Posts: n/a
Default Eliminate Duplicates in Pivot Table


Have you tried using an advanced filter for unique records only?

If items are in Col 1 and POs in col 2, you can select
Data/Filter/Advanced Filter, and specify a Copy To range. Be sure to
select "Unique records only."

This will produce a list of unique combinations of Item & PO.

The following list:
Item PO
1 a
2 b
3 c
4 a
5 b
6 c
1 a
2 c
3 b
4 a
5 c
6 b
1 d
2 e
3 f
4 d
5 e
6 f

would filter down to

Unique
Item PO
1 a
2 b
3 c
4 a
5 b
6 c
2 c
3 b
5 c
6 b
1 d
2 e
3 f
4 d
5 e
6 f

---Glenn


--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=507770

  #3   Report Post  
Old February 3rd 06, 06:13 PM posted to microsoft.public.excel.misc
ExcelPower
 
Posts: n/a
Default Eliminate Duplicates in Pivot Table


If u put PO Number in "row" & also Item Number in Row, While date/month
in "column" & Product/ Item(Count) in "data". u should get the total of
Item count per PO. It shows total of Item Per Po Number. But Can not
ignore on the ground of uniqueness. But no TWo or More Entries will be
shown of Item Per PO . I think this should solve your problem


--
ExcelPower
------------------------------------------------------------------------
ExcelPower's Profile: http://www.excelforum.com/member.php...o&userid=30964
View this thread: http://www.excelforum.com/showthread...hreadid=507770



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- how do I eliminate duplicates in count of name Isabelga Excel Discussion (Misc queries) 1 January 16th 06 06:15 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 03:56 AM
Pivot table column duplicates Kerry Excel Discussion (Misc queries) 3 September 15th 05 07:26 PM
Change Data In Pivot Table John Calder New Users to Excel 1 July 7th 05 10:41 PM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 07:24 PM


All times are GMT +1. The time now is 01:36 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017