Combining data when removing duplicates

Occasional Visitor

Morning forum, 

 

I'm looking for some help with removing duplicates in excel, I'd like to know if there is a way to sum total particular fields of data as duplicates are removed? 

 

For example, I'd like the sum of the contents of Column B when removing duplicates displayed in column A.

 

Turning this....

AB
315060900008751.75
315060900008751.75
315060900008751.75
315060900008772.81
315060900008772.81
315060900008772.81

 

Into this....

AB
315060900008755.25
315060900008778.43

 

Any help or advice would be most appreciated  

5 Replies

@LeeBrennan I'd use a pivot table.

Riny_van_Eekelen_0-1647945277669.png

 

See attached.

 

@LeeBrennan Hi,
i am with riny, because it is easiest and hassle free way

but i have also prepared formulation attached file

@LeeBrennan 

As variant

=LET(
 ref, INDEX(data,,1),
 value, INDEX(data,,2),
 u, UNIQUE(ref),
 total, MMULT(SEQUENCE(,ROWS(data),1,0), --(ref=TRANSPOSE(u) )*value  ),
IF({1,0}, u, TRANSPOSE(total)))

@LeeBrennan 

Modern Excel has so many alternatives and, as yet, little guidance concerning best practice. 

= SUMIFS(value,ref,UNIQUE(ref))

but a whole spectrum of variations is possible, including,

= MAP(UNIQUE(ref), Sumifλ(value,ref))

where "Sumifλ" is given by
= LAMBDA(v₀,r₀, LAMBDA(d, SUM(FILTER(v₀, r₀=d))))

 The purpose of the two Lambdas is to allow the sum and criterion ranges to be passed as a parameter string whilst each unique ref is passed one at a time by the MAP helper function.