+ Reply to Thread
Results 1 to 3 of 3

Eliminate Duplicates in Pivot Table

  1. #1
    roadkill
    Guest

    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. #2
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293
    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

  3. #3
    Registered User
    Join Date
    01-30-2006
    Posts
    30

    Cool I think..........

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1