Combine Near-Duplicates

Hi all!

I've got some "duplicate" data that I want to consolidate, of usernames (column A) with prices (column B) next to them.
I used quotes because I think they're technically subsets, example is below.

Column A is full of user IDs like this, in the format "Email Address (User ID)";

person@domain.com (413425)
user@place.com (315)
thing@stuff.com (1515)

But sometimes there will be a User ID or Email Address by itself that I need to combine with the full "Email (User ID)";

person@domain.com (413425)
user@place.com (315)
413425
315
thing@stuff.com (1515)

So above, I'd like to combine the price of "person@domain.com (413425)" and of "413425" into one total, prices found in column B on the same row of each.
And also to combine "user@place.com (315)" with "315" and their prices in column B.
They're the same user, the data has just been split.

There is always a "full" username with ID in brackets somewhere in column A, but there will sometimes also be "parts" of the full thing floating around somewhere down the column.
It's always either the number or the email alone.

Is there any way to consolidate all the duplicate usernames and their corresponding prices?
Let me know if more info is needed and thanks so much in advance!

Thanks a heap Dave, nearly perfect!
I just also have "Price" values in column B that correlate to the user IDs.

The total prices next to "315" and "user@place.com (315)" need to be totaled, and then any unique values should be left alone. So if "315" had $5 next to it, and "user@place.com (315)" had $6 next to it, I'd need one column to say "user@place.com (315)" and the corresponding price column to say "$11".

Once I've got that and I can eliminate leftover duplicates, I'm set! Do you know how to consolidate an extra column of prices that correlate?

D2=413425, Formula in E2

Please Login or Register  to view this content.