Remove unique values from a column using a filter from another column

Copper Contributor

Hi!

 

I have a spreadsheet that has column B with approximately 4000 letter/number strings and column C with 300. I want to delete all unique rows from column A, keeping column A sorted. 

 

I have tried =IF(ISERROR(MATCH(B2,$C$2:$C$229,0)),"Unique","Duplicate") but have had no luck. I also realized that certain cells have spaces before the text strings while the condition list does not.

 

Thanks, 

 

Kurtis

4 Replies

@kadam062 Difficult to follow what you are doing without the file to look at, but perhaps this works:

 

=IF(ISERROR(MATCH(TRIM(B2),$C$2:$C$229,0)),"Unique","Duplicate") 

 

 

I have attached the file. Your trim suggestion worked in the helper column. Now I need to delete all rows that are unique.

@kadam062 Well, you can name the helper column, put a filter on it and filter out all rows that are "Unique" to show only "Duplicate". See attached, is this what you had in mind?

@Riny_van_Eekelen yes the way you have it set it is good. I just double checked the filter and some of the data that I want to be showing up as duplicates comes up as unique (For example 1000195 on row 105).

 

I need to keep the spaces before the text in column B but I need to filter out the values that do not reappear in column C.