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

%3CLINGO-SUB%20id%3D%22lingo-sub-2292982%22%20slang%3D%22en-US%22%3ERemove%20unique%20values%20from%20a%20column%20using%20a%20filter%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2292982%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20has%20column%20B%20with%20approximately%204000%20letter%2Fnumber%20strings%20and%20column%20C%20with%20300.%20I%20want%20to%20delete%20all%20unique%20rows%20from%20column%20A%2C%20keeping%20column%20A%20sorted.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%26nbsp%3B%3DIF(ISERROR(MATCH(B2%2C%24C%242%3A%24C%24229%2C0))%2C%22Unique%22%2C%22Duplicate%22)%20but%20have%20had%20no%20luck.%20I%20also%20realized%20that%20certain%20cells%20have%20spaces%20before%20the%20text%20strings%20while%20the%20condition%20list%20does%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKurtis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2292982%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293037%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20unique%20values%20from%20a%20column%20using%20a%20filter%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037020%22%20target%3D%22_blank%22%3E%40kadam062%3C%2FA%3E%26nbsp%3BDifficult%20to%20follow%20what%20you%20are%20doing%20with%20the%20file%20to%20look%20at%2C%20but%20perhaps%20this%20works%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISERROR(MATCH(TRIM(B2)%2C%24C%242%3A%24C%24229%2C0))%2C%22Unique%22%2C%22Duplicate%22)%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293061%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20unique%20values%20from%20a%20column%20using%20a%20filter%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293061%22%20slang%3D%22en-US%22%3EI%20have%20attached%20the%20file.%20Your%20trim%20suggestion%20worked%20in%20the%20helper%20column.%20Now%20I%20need%20to%20delete%20all%20rows%20that%20are%20unique.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293543%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20unique%20values%20from%20a%20column%20using%20a%20filter%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Byes%20the%20way%20you%20have%20it%20set%20it%20is%20good.%20I%20just%20double%20checked%20the%20filter%20and%20some%20of%20the%20data%20that%20I%20want%20to%20be%20showing%20up%20as%20duplicates%20comes%20up%20as%20unique%20(For%20example%201000195%20on%20row%20105).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20keep%20the%20spaces%20before%20the%20text%20in%20column%20B%20but%20I%20need%20to%20filter%20out%20the%20values%20that%20do%20not%20reappear%20in%20column%20C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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.