# Remove duplicate and sum

1. ## 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?

2. ## Re: Remove duplicate and sum

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

3. ## Re: Remove duplicate and sum

Originally Posted by JieJenn
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. ## 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.

5. ## Re: Remove duplicate and sum

Originally Posted by JieJenn
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. ## Re: Remove duplicate and sum

Originally Posted by bob112233
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. ## Re: Remove duplicate and sum

Originally Posted by JieJenn
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. ## 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)))

9. ## 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)

10. ## Re: Remove duplicate and sum

Originally Posted by kvsrinivasamurthy
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. ## Re: Remove duplicate and sum

Originally Posted by Glenn Kennedy
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.

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

#### 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