Excel: Remove duplicate rows, but keep data from one of the columns

0
votes

While I know how to remove duplicate lines in Excel, I have a specific situation that is slightly more complex - I have a table with 10.000+ rows with most rows having duplicates or, more precisely, repeating data in all columns except one. I would need a way to automatically find these rows, remove all except one, but (and here comes the tricky part) keep the data that is currently in the only column that changes.

It will be much simpler to explain with this image:

table

As you can see in the left table, Product & Manufacturer columns repeat, while Color changes. The result I would need is like the table on the right, where Red, Blue, Orange are "aggregated" into a single cell.

Please note these are obviously not my real data; in my real scenario, I have columns A, B, C and D that are repeating while column E contains my "colors".

Any help will be greatly appreciated! :)

Thanks, Seb

1
excel
Do you know how to use macros / VBA code in Excel? Either that or probably a pivot table could help greatly. – jamheadart

1 Answers

0
votes

After some more Googling, I actually found the solution in the form of a free add-on that does specifically what I need: https://www.ablebits.com/excel-combine-rows/index.php :) Hopefully, it'll help others with my problem!