Mar 22 2022 03:17 AM
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....
A | B |
31506090000875 | 1.75 |
31506090000875 | 1.75 |
31506090000875 | 1.75 |
31506090000877 | 2.81 |
31506090000877 | 2.81 |
31506090000877 | 2.81 |
Into this....
A | B |
31506090000875 | 5.25 |
31506090000877 | 8.43 |
Any help or advice would be most appreciated :)
Mar 22 2022 03:34 AM
Mar 22 2022 06:55 AM
@LeeBrennan Hi,
i am with riny, because it is easiest and hassle free way
but i have also prepared formulation attached file
Mar 23 2022 09:01 AM
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)))
Mar 23 2022 01:03 PM
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.