+ Reply to Thread
Results 1 to 27 of 27

How to remove duplicated ID but have them add up the values the ID represneted

  1. #1
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    How to remove duplicated ID but have them add up the values the ID represneted

    Remove duplicate but add values.xlsx

    Hi,

    I have about 800 records and the the unique identifier are the names. Some owners are repeated several times and they have different values for the EDU column in my project. I need to remove any duplicated owners but I need to add up the values of each record that had value so I can get a combined value for one owner instead of a repeated owner with smaller values. How would I go by doing that? A formula or is there a tool in excel? I am lost on this one. I uploaded a sample file so you can see what I trying to do. Thanks.

    BTW i found if the values are the same for the EDU then I can not add them since it is also duplicated. I can only add up the unique values for the EDU
    Last edited by alcorp; 07-29-2014 at 05:52 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    The subtotal function should help you. Go to Data > Subtotals. Use "Sum" at every change in "Owner" for "EDU".

  3. #3
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Is there any other way? My program basically froze when I ran it. I also can't add the EDU if it is duplicated, since it is a duplicated EDU.

  4. #4
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    You have to convert the text to numbers. Select from B2 all the down to the bottom. At the top left-hand corner of B2 you'll see a little yellow icon with an "!". Click on the icon and then click "Convert to Number". Then try it again.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,186

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    This, in D2, copied down, will pull out unique ID's...
    =IFERROR(INDEX($A$2:$A$57,MATCH(0,INDEX(COUNTIF($D1:D$1,$A$2:$A$57),0,0),0)),"")

    However, there are no values to adfd in the file youi uploaded
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Okay it works but I am still trying to get rid of the duplicates and I need to just to display the Owner name without "Total" in it.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,186

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Who was this addressed to?

    Quote Originally Posted by alcorp View Post
    Okay it works but I am still trying to get rid of the duplicates and I need to just to display the Owner name without "Total" in it.

  8. #8
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Quote Originally Posted by FDibbins View Post
    Who was this addressed to?
    It was to hoyasaxa215 lol

    I haven't tried your formula yet.

  9. #9
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Quote Originally Posted by FDibbins View Post

    However, there are no values to adfd in the file youi uploaded
    The values are the EDU. I am trying to add those that have the same Owner so I can have one EDU and one OWNER

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,096

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    VBA solution, if you like

  11. #11
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Quote Originally Posted by jindon View Post
    VBA solution, if you like
    I have no idea how you created that.

    I'm only looking for one value per owner from the EDU. If the EDU is repated then it is only added onece such as


    OWNER___________________________EDU
    A T AND T COMMS OF CALIF INC____00000.5855 <----It is repeated several times
    A T AND T COMMS OF CALIF INC____00000.5855
    A T AND T COMMS OF CALIF INC____00000.5855
    A T AND T COMMS OF CALIF INC____00000.5855
    A T AND T COMMS OF CALIF INC____00004.5270 <-----This one is different

    So the Result for the EDU and the Owner is 00000.5855 + 00004.5270 = 00005.1125



    OWNER ______________________EDU
    A T AND T COMMS OF CALIF INC____00005.1125 <---What the end result should look like

  12. #12
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Hi

    See the file!
    It a array formula

    Press same time Ctrl+shift+enter,NOT ENTER copy down

    Regard
    micope21
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,096

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Like this then

  14. #14
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Quote Originally Posted by micope21 View Post
    Hi

    See the file!
    It a array formula

    Press same time Ctrl+shift+enter,NOT ENTER copy down

    Regard
    micope21
    Okay I appreciate the help and tried using your sheet but they Owner name is sill repeated. I tried adding the rest of the rows but still it seems as if there is no filter/formula that. require just one instance of the Owner name and One instance of the EDU.

    The result should be around 10 different Owner names from the example along with the one instance of the EDU. (10 and 10)
    Sort of like using the filter but I can't loose the EDU from the rows unless that EDU in that row was repeated more than once. I can only add that EDU with the matching owner name once. If the owner name is repeated ten times but has the same EDU then the EDU is not added to anything since the EDU was repeated. The only time it does add is when the EDU is different.

  15. #15
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Yes! But I have no idea how you did it with the blue block lol

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,096

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    You are welcome and thanks for the reps.

    Can you mark this thread as "Solved", if it solved your problem?

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,096

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Quote Originally Posted by alcorp View Post
    Yes! But I have no idea how you did it with the blue block lol
    Hit Alt + F11, then you can see vba codes....

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,186

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Quote Originally Posted by alcorp View Post
    The values are the EDU. I am trying to add those that have the same Owner so I can have one EDU and one OWNER
    The contents of the cells in EDU are text, not numeric

    With my 1st formula in column D, use this in E2, copied down...
    =SUMPRODUCT(($A$2:$A$57=D2)*($B$2:$B$57*1))
    Last edited by FDibbins; 07-28-2014 at 06:44 PM.

  19. #19
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    See attached. Removed duplicates from Column A using the excel feature, then did a simple "=COUNTIF". Requires minimal familiarity with excel.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Quote Originally Posted by hoyasaxa215 View Post
    See attached. Removed duplicates from Column A using the excel feature, then did a simple "=COUNTIF". Requires minimal familiarity with excel.
    It's for 800 records. So I would have to spend too much time doing it this way.

    Basically now my requirements have changed so now all I have to is basically delete the duplicates and sum up their values. I tried using the consolidating tool under data but for whatever reason it doesn't work with my data. I need a formula to display one owner for each unique owner and then the sum of all of that same owner's EDU. I tried looking online but still same issue.

  21. #21
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    The number of records is essentially irrelevant. And my previous post should have said "=SUMIF", not "=COUNTIF". Following my previous instructions will achieve your desired objective and take less than 1 minute.

  22. #22
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    I seem to not understand.

    Okay I put the this formula where I would like the results to show and I get a 0 value and no Owner. I tried messing with it, deleting duplicates and I am still lost.
    I even tried adding the information to the one you have up here and I get the same result as I had before with no owner and 0 values. I just changed the formula to this =SUMIF($A$2:$B$786,E2,$B$2:$B$786) since 786 is the last record.

  23. #23
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    You can solve it on three ways
    sumif formula
    sumproduct formula
    Or the most simple solution (I think) is the use of a pivot table.
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  24. #24
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    Thanks for all the help. Yeah the pivot table looks the best but again I did the same exact thing but When I change the values under the pivot table list from count to Sum of EDU, the pivot table produces all 0's.

    I tried convert the EDU to number, text, general and still nothing. The count works but for whatever reason the sum doesn't. Is it setting I forgot to change or something along those lines?

  25. #25
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    I did not change the celformat.
    What happen if you change the celfomat (column B) in numbers with 2 decimals?

  26. #26
    Registered User
    Join Date
    06-24-2014
    Location
    Ontario, CA
    MS-Off Ver
    2010
    Posts
    45

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    I just restarted the file. For whatever now it works, and thanks again for the help!

  27. #27
    Registered User
    Join Date
    07-22-2014
    Location
    Riyadh
    MS-Off Ver
    2010
    Posts
    88

    Re: How to remove duplicated ID but have them add up the values the ID represneted

    hi
    i think you can use pivottable.

    first convert edu text to number using formula value() or use the option convert to text

    then go to insert pivottable.select table range $A$1:$b$57 choose where you want the table.
    choose fields all.
    owner name should be select under rowlabel and edu under values.



    thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Remove Duplicated items in combo box
    By FRIEL in forum Excel General
    Replies: 11
    Last Post: 12-03-2012, 04:26 PM
  2. How to remove semi-duplicated text
    By variatsioon in forum Excel General
    Replies: 2
    Last Post: 11-05-2010, 12:49 PM
  3. Remove Combobox Duplicated items
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2009, 02:35 AM
  4. [SOLVED] How do I remove duplicated information in 2 columns of Excel?
    By GrahamR in forum Excel General
    Replies: 1
    Last Post: 10-16-2005, 08:05 AM
  5. [SOLVED] Remove ALL duplicated records, leaving behind NONE
    By Christopher Dawes in forum Excel General
    Replies: 2
    Last Post: 06-06-2005, 07:05 PM

Tags for this Thread

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