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.

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.

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.

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!

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.

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.