+ Reply to Thread
Results 1 to 11 of 11

Remove duplicate and sum

  1. #1
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Remove duplicate and sum

    Hi,

    So I know how to use excel built in function to remove duplicates and sum either using pivot table or consolidate or simply using =sumif formula after removing duplicates.

    Is it possible to create a formula where it

    a) remove the duplicate from column A
    b) sum (or perform other functions) using column B that is linked to duplicates?

    Thank you for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: Remove duplicate and sum

    What's wrong with using pivot table which does exactly what you want?

  3. #3
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Re: Remove duplicate and sum

    Quote Originally Posted by JieJenn View Post
    What's wrong with using pivot table which does exactly what you want?
    I know pivot table does what I want, but I want to learn how it can be done using formulas. Pivot table doesn't allow you to perform complex calculations only what available eg sum, average etc.

  4. #4
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: Remove duplicate and sum

    Find an empty area, in the first column starting with row 2, use this formula to list the unique list =IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($H$1:H1,$A$2:$A$8),0)),""). In column 2, use COUNTIF/SUMIF to aggregate the data.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Re: Remove duplicate and sum

    Quote Originally Posted by JieJenn View Post
    Find an empty area, in the first column starting with row 2, use this formula to list the unique list =IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($H$1:H1,$A$2:$A$8),0)),""). In column 2, use COUNTIF/SUMIF to aggregate the data.
    Ahh I see where I went wrong in my logic. you are a life saver!

    I honestly, can't get the logic behind excel right. Going from python to excel is confusing. PS- I've watched your youtube video to learn python.

  6. #6
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: Remove duplicate and sum

    Quote Originally Posted by bob112233 View Post
    Ahh I see where I went wrong in my logic. you are a life saver!

    I honestly, can't get the logic behind excel right. Going from python to excel is confusing. PS- I've watched your youtube video to learn python.
    If you have experience with Python, I would highly recommend invest your time in pandas package. Life saver for reporting and business analysis.

  7. #7
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Re: Remove duplicate and sum

    Quote Originally Posted by JieJenn View Post
    If you have experience with Python, I would highly recommend invest your time in pandas package. Life saver for reporting and business analysis.
    Yes i agree, but at the moment I'm trying to learn everything and everything about excel that relevant to what I want to achieve. I'm trying to get my head around how to effectively use formulas and hopefully down the road look into macros and vba.

    The users on this site are very helpful and i'm learning a great deal here.

    Once again thanks for your help!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Remove duplicate and sum

    Mmmm. Why not make use of 365's dynamic arrays:

    =LET(n,A2:A8,s,B2:B8,u,UNIQUE(n),CHOOSE({1,2},u,SUMIF(n,u,s)))
    Attached Files Attached Files
    Glenn



  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,095

    Re: Remove duplicate and sum

    Try this.
    IN J2

    =UNIQUE($A$2:$A$8)

    In K2 copied down

    =SUMIF($A$2:$A$8,$J2,$B$2:$B$8)
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Re: Remove duplicate and sum

    Quote Originally Posted by kvsrinivasamurthy View Post
    Try this.
    IN J2

    =UNIQUE($A$2:$A$8)

    In K2 copied down

    =SUMIF($A$2:$A$8,$J2,$B$2:$B$8)
    You read my mind, I came across this function after I looked into dynamic functions.

  11. #11
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Re: Remove duplicate and sum

    Quote Originally Posted by Glenn Kennedy View Post
    Mmmm. Why not make use of 365's dynamic arrays:

    =LET(n,A2:A8,s,B2:B8,u,UNIQUE(n),CHOOSE({1,2},u,SUMIF(n,u,s)))
    Thanks for this Glenn, I love how there are unique solutions to a simple problem. One thing I haven't mastered is how do i tell excel to "spill" or force results into neighbouring cells.

    I always seem to do things at 1 column or 1 row at a time.

    But in your solution, you seem to hit 2 birds with 1 stone.

+ 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. Replies: 3
    Last Post: 04-26-2017, 08:01 PM
  2. [SOLVED] How to get reference of duplicate value and set remove duplicate value for future entries
    By bala04msw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-20-2016, 08:13 AM
  3. [SOLVED] How to sum duplicate values then remove the duplicate rows?
    By terry3218 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-12-2015, 02:38 AM
  4. [SOLVED] MultiLine Textbox to auto remove blank line, count line, remove duplicate
    By khhoa in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-24-2015, 09:28 PM
  5. Code to remove compare and remove duplicate value between 2 columns
    By winmaxservices1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-22-2015, 02:30 PM
  6. Remove duplicate
    By tanmanoj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2014, 12:31 PM
  7. excel macro to remove specific columns and rows + remove duplicate
    By garrywelson in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-17-2013, 12:03 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