How to remove all lines where one column has a duplicate in Excel?

Question

I have a an excel table like this (pretend the commas are separators):

bill, cocacola
dan, pepsi
dan, fanta
phil, sprite
chris, redbull

See how dan has two sodas? What I want to do is remove all lines where dan is duplicated. If I just do filter by unique records only, then it won't catch that because 'dan, pepsi' and 'dan,fanta' are not duplicates.

In other words, any time a value in column A is a duplicate of another value in column A, I want the whole line removed so I'm only left with one value. It doesn't matter which one it picks...it can be the first or last, whatever.

Answer 1

You can do that if you open the spreadsheet as a database in a program that lets you do regex or AQL on a field. To Excel, dan, pepsi and dan, sprite aren't the same, and since dan and charlie aren't the same number of characters, you can't use the leftmost x characters to key on either.



If you want to write some VBA you could split on the comma, but the code would get messy quickly.

Answer 2

It seems your question is a bit confusing. You indicate you wish to remove the text string in B1 from the complete address string in A1. This would leave the remaining 'address' in A1. Then, you state that you wish to have 'A1=address, B1=city, C1=State and Zip Code. If you remove the text string in B1 (7E Cherry St), from the text string in A1, B1 will still contain the street address. That conflicts with the first part of your question. Can you provide additional information as to what your goal is? Do you just want to take the address in A1 and break it up into columns A:C?

Answer 3

Ahm., do you wanna do this?

bill, cocacola

dan, pepsi

____fanta

phil, sprite

chris, redbull



If it is so, what u gotta do is join the two cells where dan is.