# Combining data when removing duplicates

Occasional Visitor

# Combining data when removing duplicates

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

5 Replies

# Re: Combining data when removing duplicates

@LeeBrennan I'd use a pivot table.

See attached.

# Re: Combining data when removing duplicates

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

but i have also prepared formulation attached file

# Re: Combining data when removing duplicates

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)))``````

# Re: Combining data when removing duplicates

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.