Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table- how do I eliminate duplicates in count of name | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Pivot table column duplicates | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel | |||
Pivot Table Problems | Excel Discussion (Misc queries) |