Home

Removing duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-737636%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737636%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369954%22%20target%3D%22_blank%22%3E%40MMcDonal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20This%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Select%20the%20range%20you%20want%20to%20remove%20the%20duplicates%20from%2C%20Go%20into%20the%20Data%20Tab%2C%20under%20the%20data%20tools%20group%20click%26nbsp%3B%3CSTRONG%3ERemove%20Duplicates%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E*%20Check%20the%20two%20column%20you%20want%20to%20remove%20the%20Duplicates%20from%20and%20click%20OK%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20434px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122165iE630B72E469EA3B5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.PNG%22%20title%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-742719%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742719%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367553%22%20target%3D%22_blank%22%3E%40Excel_World_Champion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20EWC.%26nbsp%3B%20I%20believe%20your%20method%20would%20remove%20all%20duplicate%20values%20irrespective%20of%20the%20duplicate%20values%20based%20on%20different%20sources.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20screenshot%20below%20of%20a%20larger%20spreadsheet%20where%20there%20are%20duplicate%20values.%26nbsp%3B%20My%20goal%20is%20to%20remove%20only%20duplicate%20values%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%20source%20only%3C%2FSPAN%3E.%26nbsp%3B%20So%20in%20this%20case%2C%20removal%20of%26nbsp%3B%3CSPAN%3Eall%26nbsp%3B%3C%2FSPAN%3Eduplicate%20values%20by%20source%20(name)%2C%20including%20the%20%243%2C000%20duplicate%20value%20for%20GenD%2C%3CSPAN%3E%26nbsp%3Bbut%20no%3C%2FSPAN%3E%3CSPAN%3Et%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Efor%20Henry%20S.%20and%20HewP%20since%20they%20are%20separate%20sources%2Fnames.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%243%2C000%20values%20for%20GenD%20and%20HenryS%20should%20remain%20intact%2Fnot%20removed%2C%20since%20they%20are%20separate%20sources%20of%20the%20duplicate%20values%20within%20the%20range%20(spreadsheet).%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%20322px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122559iF4B33C4E906050C6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Removal%20of%20Duplicate%20Values%20per%20Each%20Source.png%22%20title%3D%22Removal%20of%20Duplicate%20Values%20per%20Each%20Source.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMMcDonal%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743322%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369954%22%20target%3D%22_blank%22%3E%40MMcDonal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20remove%20by%20source%20select%20all%20columns%20in%20your%20range%20and%20check%20only%20one%20with%20source%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20373px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122602iBCD53E8EB0159E19%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EResult%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20222px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122603i41ACF6B5B162F318%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-747376%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-747376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Sergei%2C%20particularly%20since%20it%20is%20a%20function%20I%20will%20need%20to%20use%20repeatedly%20on%20numerous%20similar%20projects%20going%20forward.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-748902%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369954%22%20target%3D%22_blank%22%3E%40MMcDonal%3C%2FA%3E%26nbsp%3B%2C%20Power%20Query%20could%20be%20a%20variant%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-734474%22%20slang%3D%22en-US%22%3ERemoving%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-734474%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20remove%20duplicate%20values%20associated%20with%20a%20name%20only%3F%26nbsp%3B%20I%20have%20a%20number%20of%20the%20duplicate%20values%20that%20are%20repeated%20across%20a%20table%20range%20and%20correspond%20to%20different%20names%20(in%20the%20column%20to%20the%20right%20of%20the%20values.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20want%20to%20delete%20duplicate%20values%20associated%20with%20each%20corresponding%20name%20only%2C%20and%20not%20all%20duplicate%20values%20in%20the%20table%20range.%26nbsp%3B%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-734474%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
MMcDonal
New Contributor

 

How do I remove duplicate values associated with a name only?  I have a number of the duplicate values that are repeated across a table range and correspond to different names (in the column to the right of the values.)

 

I only want to delete duplicate values associated with each corresponding name only, and not all duplicate values in the table range.  Thanks.

 

5 Replies

Hey @MMcDonal 

 

Try This:

 

* Select the range you want to remove the duplicates from, Go into the Data Tab, under the data tools group click Remove Duplicates:

* Check the two column you want to remove the Duplicates from and click OK

 

1.PNG

 

 

@Excel_World_Champion 

 

Thanks EWC.  I believe your method would remove all duplicate values irrespective of the duplicate values based on different sources.

 

See screenshot below of a larger spreadsheet where there are duplicate values.  My goal is to remove only duplicate values by source only.  So in this case, removal of all duplicate values by source (name), including the $3,000 duplicate value for GenD, but not for Henry S. and HewP since they are separate sources/names. 

 

The $3,000 values for GenD and HenryS should remain intact/not removed, since they are separate sources of the duplicate values within the range (spreadsheet).

 

Removal of Duplicate Values per Each Source.png

 

Thanks!

 

MMcDonal

@MMcDonal 

To remove by source select all columns in your range and check only one with source

image.png

Result is

image.png

@Sergei Baklan 

 

Thank you Sergei, particularly since it is a function I will need to use repeatedly on numerous similar projects going forward.  

@MMcDonal , Power Query could be a variant