Home

How do I remove rows with duplicate values?

%3CLINGO-SUB%20id%3D%22lingo-sub-314666%22%20slang%3D%22en-US%22%3EHow%20do%20I%20remove%20rows%20with%20duplicate%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314666%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20Excel%20file%20that%20I%20combined%20from%202%20different%20reports.%26nbsp%3B%20Column%20A%20is%20%22User%20ID%22.%26nbsp%3B%20I%20want%20to%20remove%20the%20rows%20where%20User%20ID%20match.%26nbsp%3B%20I%20can't%20use%20the%20Remove%20Duplicates%20option%20because%20only%20one%20of%20the%20rows%20would%20be%20removed.%26nbsp%3B%20I%20want%20BOTH%20rows%20removed%20so%20I%20am%20left%20with%20only%20the%20User%20IDs%20that%20were%20NOT%20on%20both%20reports.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-314666%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314816%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20remove%20rows%20with%20duplicate%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314816%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%26nbsp%3B%20You%20have%20been%20really%20helpful!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314770%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20remove%20rows%20with%20duplicate%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314770%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20formula%20is%20correct!%3C%2FP%3E%3CP%3EBut%20you%20need%20to%20put%20it%20in%20cell%20B2%20and%20drag%20it%20down.%3C%2FP%3E%3CP%3EThen%20sort%20the%20column%20and%20delete%20the%20dups%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20dups%20starts%20from%20row%202891%2C%20just%20select%20them%20all%20and%20press%20delete.%3C%2FP%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%2F65995iA0D8047DD4ECE103%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22COUNTIF2.png%22%20title%3D%22COUNTIF2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314763%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20remove%20rows%20with%20duplicate%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314763%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20you%20tell%20me%20what%20I'm%20doing%20wrong%3F%26nbsp%3B%20See%20attached%20please.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314756%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20remove%20rows%20with%20duplicate%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314756%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20recommend%20you%20to%20use%20COUNTIF%20as%20the%20Power%20Query%20need%20multiple%26nbsp%3Bsteps%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3Ethat%20are%20difficult%20to%20explain%20here.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20COUNTIF%20formula%3A%3C%2FP%3E%3CPRE%3E%3DCOUNTIF(%24A%242%3A%24A%247%2CA2)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20732px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F65992i16F8F3B115F0983F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22COUNTIF.png%22%20title%3D%22COUNTIF.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20you%20enter%20this%20formula%20in%20a%20new%20column%20as%20the%20screenshot%20above%2C%20you%20have%20to%20sort%20the%20column%20from%20smallest%20to%20largest%2C%20and%20then%20delete%20the%20entire%20rows%20that%20are%20greater%20than%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20learn%20more%20about%20COUNTIF%2C%20please%20check%20out%20this%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FCOUNTIF-function-E0DE10C6-F885-4E71-ABB4-1F464816DF34%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20makes%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314745%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20remove%20rows%20with%20duplicate%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314745%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20that%20is%20what%20I%20am%20asking%20for%2C%20thank%20you.%26nbsp%3B%20Unfortunately%2C%20I%20don't%20know%20how%20to%20use%20the%26nbsp%3B%3CSPAN%3ECOUNTIF%20function%20or%20perform%20a%20Power%20Query.%26nbsp%3B%20Do%20you%20have%20a%20job%20aid%20or%20instructions%26nbsp%3BI%20could%20follow%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314742%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20remove%20rows%20with%20duplicate%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314742%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20take%20a%20look%20at%20the%20screenshot%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20763px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F65989i5D56E821C48E4200%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Remove%20both%20duplicate%20rows.png%22%20title%3D%22Remove%20both%20duplicate%20rows.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20that%20what%20you%20asking%20for%3F%3C%2FP%3E%3CP%3EIf%20so%2C%20you%20can%20use%20COUNTIF%20function%20in%20a%20helper%20column%20to%20return%20the%20number%20of%20occurrences%20for%20each%20ID%2C%20then%20sort%20it%20from%20smallest%20to%20largest%2C%20and%20then%20delete%20the%20rows%20that%20are%20greater%20than%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20also%20use%20Power%20Query%20to%20do%20so.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jason32
New Contributor

I have a large Excel file that I combined from 2 different reports.  Column A is "User ID".  I want to remove the rows where User ID match.  I can't use the Remove Duplicates option because only one of the rows would be removed.  I want BOTH rows removed so I am left with only the User IDs that were NOT on both reports.

6 Replies

Hi,

 

Please take a look at the screenshot below.

 

Remove both duplicate rows.png 

Is that what you asking for?

If so, you can use COUNTIF function in a helper column to return the number of occurrences for each ID, then sort it from smallest to largest, and then delete the rows that are greater than one.

 

You can also use Power Query to do so.

 

Please find the attached file.

 

Hope that helps

Yes, that is what I am asking for, thank you.  Unfortunately, I don't know how to use the COUNTIF function or perform a Power Query.  Do you have a job aid or instructions I could follow?

Hi,

 

I would recommend you to use COUNTIF as the Power Query need multiple steps that are difficult to explain here.

 

This is the COUNTIF formula:

=COUNTIF($A$2:$A$7,A2)

COUNTIF.png

 

After you enter this formula in a new column as the screenshot above, you have to sort the column from smallest to largest, and then delete the entire rows that are greater than one.

 

To learn more about COUNTIF, please check out this link.

 

Hope that makes sense.

Can you tell me what I'm doing wrong?  See attached please.

This formula is correct!

But you need to put it in cell B2 and drag it down.

Then sort the column and delete the dups rows.

 

The dups starts from row 2891, just select them all and press delete.

COUNTIF2.png

Thank you!  You have been really helpful!