Removing duplicates from a list, while also adding frequencies

Hello everyone,

I have run into a problem that I have a hard time figuring out myself. I was hoping that maybe someone here could help me out. For scientific research on the repertoire of immune cells, I have a list with species, and the frequency of such a species. This data is then analysed by a computer program that models diversity in this repertoire. However, I have run into a problem with the way my data is organised. The data is as such:

Species Frequency
A 15
B 10
C 12
A 5

However, the program needs input as such:

Species Frequency
A 20
B 10
C 12

As you can see, the duplicate A has been removed and added to the first instance of A found. Is there anyway I could easily do this with excel?
The lists I have each contain about 60.000 rows, so I hope to find something that can be automated and does not take to much computing power.

Does anyone have a solution or a tip for this problem?

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

I have a simple clip of how to use a pivot table for your data set.

https://www.screencast.com/t/caRP9PMo0

VBA solution.

Please Login or Register  to view this content.

Thank you for all your suggestions. The solution with the pivot tables is working, however it is still quite a bit of work to create these pivot tables. I would prefer using the VBA solution. However, I am a bit new in VBA, and cant get it to work. When i try to run this VBA code I get a "object required" error. When I use debug mode, it seems to be this line that causes problems:
sn = Blad1.Cells(1).CurrentRegion.Value

That line means your data starts in A1.
I've put the code in your example sheet so you can see it works.

Originally Posted by SiebeB

Thank you for your effort. However, the code is not doing anything on my pc. Could it be different excel versions?

The results are on the other sheet

Thanks for feedback and rep+.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.