SOLVED

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%3CLINGO-SUB%20id%3D%22lingo-sub-2392667%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-2392667%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BThank%20you%20for%20this%20information!%26nbsp%3B%20This%20solves%20part%20of%20my%20issue.%20I%20have%20an%20ID%20in%20Column%20A%20(some%20unique%20and%20some%20duplicates)%2C%20Version%20number%20in%20column%20B%20(blank%20is%20the%20first%20version%2C%20then%20version%201%2C%20version%202%2C%20etc.)%20and%20Amount%20in%20column%20C.%26nbsp%3B%20I%20want%20to%20add%20the%20Amount%20in%20Column%20C%20for%20the%20highest%20Version%20(column%20B)%20for%20each%20ID%20(column%20A)%20only%20once.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20it%20is%20possible%20that%20there%20may%20be%20more%20than%20one%20ID%20with%20the%20same%20version%20with%20different%20amounts.%26nbsp%3B%26nbsp%3BCould%20these%20be%20flagged%20for%20human%20review%20to%20determine%20which%20one%20to%20sum%20e.g.%20%23Error%20if%20they%20are%20the%20highest%20Version%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
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!

3 Replies
best response confirmed by thacknology (New Contributor)
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.

@Haytham Amairah Thank you for this information!  This solves part of my issue. I have an ID in Column A (some unique and some duplicates), Version number in column B (blank is the first version, then version 1, version 2, etc.) and Amount in column C.  I want to add the Amount in Column C for the highest Version (column B) for each ID (column A) only once. 

 

Also, it is possible that there may be more than one ID with the same version with different amounts.  Could these be flagged for human review to determine which one to sum e.g. #Error if they are the highest Version?