Eliminate Duplicates in Pivot Table

[Guest]

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

 

[gjcase]

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

If items are in Col 1 and POs in col 2, you can selec
Data/Filter/Advanced Filter, and specify a Copy To range. Be sure t
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

---Glen

 

[ExcelPower]

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