Conditionally remove duplicate data

Hi everyone,

I want to get rid of duplicate data e.g. my data has "303 Air and spacecraft and related machinery" and "3030 Air and spacecraft and related machinery."

However I can't simply get rid of the duplicate data the normal way because the data is replicated for 26 other countries so simply getting rid of "Air and spacecraft and related machinery" would get rid of the values for the other 26 countries and also, the cell is different because one has code "303" and one has code "3030" even though the data is the same.

Attaching a sample data set. Bearing in mind that the original data has over 140 products per country with an unknown amount of duplications, is there any way to solve this? Perhaps using some sort of macro?

as long as only numbers changes and the text remains the same then remove the number with (change your data into a table)

Please Login or Register  to view this content.

Then you can easily sort or remove duplicates

Originally Posted by gue2013

as long as only numbers changes and the text remains the same then remove the number with (change your data into a table)

Please Login or Register  to view this content.

Then you can easily sort or remove duplicates

Thanks! It'd be great if you could elaborate a little about how the formula works? so I can replicate it for my data set as I don't quite understand it

Originally Posted by jeffreybrown

Does this help? Use a couple of helper columns and then filter by column G to delete the dups. A value in column G greater than 1 means dup.

Thanks! This definitely does help but the dataset I'm working on will have to be updated with new data every year or so. It'd be great if there was a formula/macro that could be replicated so when the next person updates it with new data, it's easy for them to delete duplicates rather than having to make another column etc.

First convert your data to a table (CTRL t) with headers

then put this formula at the right side top of your data (you can change the heading later or before you add data)
put the formula into this cell

Please Login or Register  to view this content.

three functions are used

• right(input text, len) --> the output is the text with number of characters from the end of the input string
• len(input strin) --> number of characters of the string
• find( text to find, input string) --> text to find in input string

[@[column name]] -- data in column