Can you remove duplicates from Pivot Table?

Discussion in 'Microsoft Excel Misc' started by Guest, Oct 24, 2006.

  1. Guest

    Guest Guest

    Hi there - i have a database which looks something like this:
    Name Number
    Fred 1000
    Fred 1000
    Fred 1000
    Tom 2000
    Tom 2000
    Tom 2000
    Jane 3000
    Jane 3000
    Jane 3000

    I want my pivot table to show:
    Fred 1000
    Tom 2000
    Jane 3000

    is there a way to remove duplicate data?

    right now my pivot table shows:
    Fred 3000
    Tom 6000
    Jane 9000

    I don't want the numbers to be summed.

    Alternative solutions i have already looked at:
    I know I can also do this using Advanced Filtering - which I will do if need
    be.
    I have also added a new column to my database called "Unique entries" which
    i can use for my pivot table - but that's another "manual" thing to do - i
    would like to try to work with the existing database - without having to add
    more columns and formulas.

    appreciate your assistance!
     
    Guest, Oct 24, 2006
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    You could average the numbers. That would work in the example you posted.
    Just change the aggregation of the numbers from sum to average.
    --
    HTH...

    Jim Thomlinson


    "Mississauga99" wrote:

    > Hi there - i have a database which looks something like this:
    > Name Number
    > Fred 1000
    > Fred 1000
    > Fred 1000
    > Tom 2000
    > Tom 2000
    > Tom 2000
    > Jane 3000
    > Jane 3000
    > Jane 3000
    >
    > I want my pivot table to show:
    > Fred 1000
    > Tom 2000
    > Jane 3000
    >
    > is there a way to remove duplicate data?
    >
    > right now my pivot table shows:
    > Fred 3000
    > Tom 6000
    > Jane 9000
    >
    > I don't want the numbers to be summed.
    >
    > Alternative solutions i have already looked at:
    > I know I can also do this using Advanced Filtering - which I will do if need
    > be.
    > I have also added a new column to my database called "Unique entries" which
    > i can use for my pivot table - but that's another "manual" thing to do - i
    > would like to try to work with the existing database - without having to add
    > more columns and formulas.
    >
    > appreciate your assistance!
     
    Guest, Oct 24, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    thanks Jim for that great idea!

    "Jim Thomlinson" wrote:

    > You could average the numbers. That would work in the example you posted.
    > Just change the aggregation of the numbers from sum to average.
    > --
    > HTH...
    >
    > Jim Thomlinson
    >
    >
    > "Mississauga99" wrote:
    >
    > > Hi there - i have a database which looks something like this:
    > > Name Number
    > > Fred 1000
    > > Fred 1000
    > > Fred 1000
    > > Tom 2000
    > > Tom 2000
    > > Tom 2000
    > > Jane 3000
    > > Jane 3000
    > > Jane 3000
    > >
    > > I want my pivot table to show:
    > > Fred 1000
    > > Tom 2000
    > > Jane 3000
    > >
    > > is there a way to remove duplicate data?
    > >
    > > right now my pivot table shows:
    > > Fred 3000
    > > Tom 6000
    > > Jane 9000
    > >
    > > I don't want the numbers to be summed.
    > >
    > > Alternative solutions i have already looked at:
    > > I know I can also do this using Advanced Filtering - which I will do if need
    > > be.
    > > I have also added a new column to my database called "Unique entries" which
    > > i can use for my pivot table - but that's another "manual" thing to do - i
    > > would like to try to work with the existing database - without having to add
    > > more columns and formulas.
    > >
    > > appreciate your assistance!
     
    Guest, Oct 24, 2006
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Pivot table column duplicates

    Guest, Sep 15, 2005, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    182
    Debra Dalgleish
    Sep 15, 2005
  2. Guest

    My pivot table field list show duplicates

    Guest, Dec 6, 2005, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    374
    Guest
    Dec 6, 2005
  3. Guest
    Replies:
    1
    Views:
    362
    Guest
    Jan 16, 2006
  4. Guest

    Eliminate Duplicates in Pivot Table

    Guest, Feb 2, 2006, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    4,127
    ExcelPower
    Feb 3, 2006
  5. Replies:
    4
    Views:
    277
Loading...

Share This Page