+ Reply to Thread
Results 1 to 11 of 11

Looking for function to help ease my job

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Question Looking for function to help ease my job

    I have an excel spreadsheet with 5762 records. I have used conditional formatting to highlight the cells containing duplicates values. I need to then add those duplicates up.

    IE:

    ID BAL
    1 10
    1 10

    I need to figure out a way to add the balances together based on them being the same id# if thats even possible. I sorted my sheet out by cell color and I have a thousand or duplicates highlighted. I have been manually going to the cells and doing =sum(i1,i2)

    Any help would be greatly appreciated, I have a feeling I will be getting more data with the same problem I am facing now.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Looking for function to help ease my job

    Something like this?

    sumproduct-duplicates.xlsx

  3. #3
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Looking for function to help ease my job

    You should use sumif

    I am not really sure if you want a list of all the unique values and the sum of the old list with duplicates? if that is the case then you can copy your ID column and on sheet2 paste it. now in your ribbon click data, in the middle of the bar you should see "remove duplicates", push that button. You are now left with unique values, then in column B use this formula and adjust to the range that you need and copy all the way to the end of column A

    =SUMIF('Sheet1'!$A$2:$A$100,A1,$B$2:$B$100)
    Regards,
    amotto

    If I helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Looking for function to help ease my job

    use SUMIF function

    =SUMIF($B$1:$B$100,$A$1:$A$100,A1)

    Where $B$1:$B$100 is the range to sum up
    $A$1:$A$100 is the range of IDs
    A1 is the first ID
    Put this formula in cell C1 then copy this formula all the cells up to C100

  5. #5
    Registered User
    Join Date
    07-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Looking for function to help ease my job

    My excel spreadsheet has more columns and data and I don't want to totally remove the duplicates as I want to add only the balance columns that have the same id # if that makes sense.

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Looking for function to help ease my job

    My post above is only calculating the totals of duplicates.

  7. #7
    Registered User
    Join Date
    07-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Looking for function to help ease my job

    Opening now was sketchy because I did not notice it was an attachment thought it was a website ha

  8. #8
    Registered User
    Join Date
    07-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Looking for function to help ease my job

    Is there anything special I need to do, or do I need to create an additional column

  9. #9
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Looking for function to help ease my job

    Yes, as the other contributors suggested you just need a column of unique values somewhere.

    For example, assume you have put it in Column O:

    O1 = 1, O2 =IF(COUNTIF($A$2:$A$8,O1)>1,SUMPRODUCT(($A$2:$A$8=O1)*($B$2:$B$8)),"No Duplicates")

    Once you have your list in O, and the first formula entered in O2, you can copy and paste it down for every other ID in O.



    Formula Logic: If Unique ID exists more than once, add up every instance of balance. If it only exists once, say "No Duplicates"

  10. #10
    Registered User
    Join Date
    07-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Looking for function to help ease my job

    I got it working thanks to you guys/gals

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Looking for function to help ease my job

    No problem. I usually learn something from each request.

+ 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. Ease of Number Entry
    By paradox80 in forum Excel General
    Replies: 1
    Last Post: 03-06-2006, 06:55 PM
  2. [SOLVED] Financial projections calendar in a Outlook-style for ease of use
    By Scott B22060 in forum Excel General
    Replies: 0
    Last Post: 11-20-2005, 04:55 PM
  3. Need help with code location PL EASE
    By PCOR in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2005, 10:06 AM
  4. Ease of use database
    By da_tee in forum Excel General
    Replies: 0
    Last Post: 02-03-2005, 01:48 PM
  5. ease of use database
    By da_tee in forum Excel General
    Replies: 0
    Last Post: 02-03-2005, 01:47 PM

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