A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Eliminate Duplicates in Pivot Table



 
 
Thread Tools Display Modes
  #1  
Old February 2nd 06, 05:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
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
Ads
  #2  
Old February 2nd 06, 09:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old February 3rd 06, 06:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 06:00 AM.


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