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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Guest

    Pivot Table and Pivot Table Reports

    Guest, Mar 22, 2004, in forum: Microsoft Excel Misc
    Replies:
    4
    Views:
    1,035
    Dave Peterson
    Mar 26, 2004
  2. Mandy11

    Pivot table "save data in pivot table" option

    Mandy11, Aug 19, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    1,093
    Debra Dalgleish
    Aug 19, 2004
  3. Mandy11
    Replies:
    0
    Views:
    540
    Mandy11
    Aug 24, 2004
  4. Eva Shanley

    pivot table: create worksheetsheet using pivot table

    Eva Shanley, Sep 16, 2004, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    486
    Eva Shanley
    Sep 16, 2004
  5. icestationzbra

    Re: pivot table: create worksheetsheet using pivot table

    icestationzbra, Sep 16, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    458
    Guest
    Sep 16, 2004
  6. Guest
    Replies:
    0
    Views:
    372
    Guest
    Sep 16, 2004
  7. Guest
    Replies:
    1
    Views:
    416
    Guest
    Jun 1, 2005
  8. Guest
    Replies:
    1
    Views:
    3,180
    Dave Peterson
    Jun 23, 2005
Loading...