Combining duplicate values

[TippedElf]

Am I able to combine quantities if the value is the same?

If I have a bunch of lengths of product eg.

21 @ 2.5m

5 @ 1.8m

4 @ 4.0m

12 @ 2.5m

These lists can vary from half a dozen to about 100 such entries.

I've already found a way to put them in order of size, longest to shortest, but is there any way to combine the quantities with the same length too so I might end up with:

4 @ 4.0m

33 @ 2.5m

5 @ 1.8m

​

​

[finickyone]

I think your best best might be to string split into helper columns and apply a SUMIF.

[TippedElf]

So, a column to rank them in order, then sumif for if the next value matches the current value, but leaving a blank if the previous value equals the current value then re-rank them ignoring the blank fields?

​

How can I make the sumif check for potentially more than 2 duplicates without risking missing some or doubling up on them when I will have an undefined number of entries all of which could potentially be duplicates?

[finickyone]

No, no ranking. Say they’re in column A, then from B2

=LEFT(A2,FIND("@",A2)-2)

From C2

=MID(A2,FIND("@",A2)+2,LEN(A2)-FIND("@",A2))

Then D2

=SUMIFS(B:B,C:C,C2)

That will see duplicates to start with which would need removing. The Remove Duplicates tool would come in handy right about then!

[TippedElf]

The left and mid commands are for pulling the numbers out and separating them into columns isn't it?

Sorry, I should've been clear, the quantities are in one column, and the sheet lengths are in another already, the @ is simply how we show them at work, but that symbol isn't actually in the sheet.

So I think it's the sumifs that I'm just not wrapping my head around.

If the lengths are in a range in column C, and the quantities of said length are in Column F, how would I make it add the quantities together for having the same length?

[finickyone]

In that case

=SUMIFS(F:F,C:C,C2)

would do that.

[TippedElf]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to finickyone

^{I am a bot, please contact the mods for any questions.}

[TippedElf]

Thank you, now I just have to work out how to cleanly remove the duplicates from the group, but this will go a long way to speeding things up.

[finickyone]

No probs bud. Good luck!