SOLVED
Home

How do I choose which row to keep in Remove Duplicates?

%3CLINGO-SUB%20id%3D%22lingo-sub-785186%22%20slang%3D%22en-US%22%3EHow%20do%20I%20choose%20which%20row%20to%20keep%20in%20Remove%20Duplicates%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785186%22%20slang%3D%22en-US%22%3E%3CP%3EHiya.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20several%20thousand%20rows%20that%20have%20data%20in%20the%20URL%20column%20in%20common.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20Remove%20Duplicates%20using%20data%20in%20this%20column%20to%20signify%20which%20row%20is%20a%20duplicate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20data%20elsewhere%20in%20each%20row%20may%20be%20different.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20I%20want%20to%20remove%20duplicates%20based%20on%20that%20URL%20data%2C%20I%20also%20want%20to%20choose%20which%20rows%20to%20%3CSTRONG%3Ekeep%3C%2FSTRONG%3Ebased%20on%20the%20contents%20of%20another%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ESay%20two%20rows%20have%20the%20same%20URL%20-%20example.com%3C%2FLI%3E%3CLI%3EIn%20the%20Paragraphs%20column%2C%20one%20row%20has%200%2C%20and%20the%20other%20has%20any%20other%20number.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EHow%20can%20I%20remove%20only%20the%20duplicate%20row%20that%20has%200%20in%20the%20Paragraphs%20column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-785186%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785562%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20choose%20which%20row%20to%20keep%20in%20Remove%20Duplicates%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386197%22%20target%3D%22_blank%22%3E%40thacknology%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20can%20be%20done%20by%20using%20a%20formula%20in%20a%20helper%20column%20to%20identify%20the%20columns%20that%20you%20need%20to%20delete.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DIF(COUNTIF(%24B%242%3A%24B%2410%2CB2)%26gt%3B1%2CIF(A2%3D0%2C%22To%20be%20removed%22%2C%22%22)%2C%22%22)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125394iE8CA485296A01255%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Delete%20specific%20columns.png%22%20title%3D%22Delete%20specific%20columns.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20that%2C%20you%20can%20sort%20the%20helper%20column%20from%20A%20to%20Z%2C%20then%20select%20the%20marked%20columns%20and%20delete%20them.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20909px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125395iEB466D2DB562E8FA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Delete%20specific%20columns2.png%22%20title%3D%22Delete%20specific%20columns2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785566%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20choose%20which%20row%20to%20keep%20in%20Remove%20Duplicates%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785566%22%20slang%3D%22en-US%22%3EIt's%20impossible%20that%20this%20answer%20can%20be%20bettered.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20so%20much%20for%20taking%20the%20time%20to%20answer.%20It's%20greatly%20appreciated.%3C%2FLINGO-BODY%3E
thacknology
New Contributor

Hiya.

 

I have several thousand rows that have data in the URL column in common.

 

I want to Remove Duplicates using data in this column to signify which row is a duplicate.

 

But data elsewhere in each row may be different.

 

While I want to remove duplicates based on that URL data, I also want to choose which rows to keep based on the contents of another cell.

 

  • Say two rows have the same URL - example.com
  • In the Paragraphs column, one row has 0, and the other has any other number.

How can I remove only the duplicate row that has 0 in the Paragraphs column?

 

Thank you!

2 Replies
Solution

@thacknology

 

Hi,

 

This is can be done by using a formula in a helper column to identify the columns that you need to delete.

 

I suggest this formula:

=IF(COUNTIF($B$2:$B$10,B2)>1,IF(A2=0,"To be removed",""),"")

Delete specific columns.png

 

After that, you can sort the helper column from A to Z, then select the marked columns and delete them.

Delete specific columns2.png

 

 

Hope that helps

It's impossible that this answer can be bettered.

Thank you so much for taking the time to answer. It's greatly appreciated.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies