SOLVED
Home

Remove only duplicates that do not have hyperlinks

%3CLINGO-SUB%20id%3D%22lingo-sub-239528%22%20slang%3D%22en-US%22%3ERemove%20only%20duplicates%20that%20do%20not%20have%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239528%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%202016%3C%2FP%3E%3CP%3EI%20have%20duplicates%20in%20my%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20duplicate%20value%20is%20hyperlinked%2C%20one%20duplicate%26nbsp%3Bvalue%20is%20not.%20Not%20all%20hyperlinks%20are%20duplicates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20formulas%20are%20used%2C%20just%20data%20and%20hyperlinks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20remove%20the%20duplicates%20that%20do%20not%20have%20hyperlinks.%20Any%20thoughts%3F%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-239528%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239884%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20only%20duplicates%20that%20do%20not%20have%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239884%22%20slang%3D%22en-US%22%3Eperfect!%20Each%20duplicate%20information%20column%20also%20has%20a%20column%20with%20dates%20which%20change%20weekly%20so%20I%20just%20sorted%20them%20by%20date%20in%20one%20column%20(oldest%20date%20first)%2C%20found%20and%20sorted%20by%20duplicates%20and%20ran%20the%20remove%20duplicates%20again.%20Thanks%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239811%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20only%20duplicates%20that%20do%20not%20have%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239811%22%20slang%3D%22en-US%22%3EWhen%20Excel%20removes%20duplicates%2C%20it%20leaves%20the%20first%20line%20in%20place%20of%20each%20duplicate.%20So%20if%20you%20sort%20the%20table%20in%20a%20smart%20way%20so%20the%20hyperlinked%20data%20is%20on%20top%2C%20you%20remove%20the%20ones%20without%20hyperlinks.%3CBR%20%2F%3EMake%20sure%20the%20filter%20arrows%20are%20visible%20(Select%20all%20data%2C%20click%20the%20filter%20icon%20on%20the%20Data%20tab).%20Then%20click%20the%20arrow%20on%20the%20column%20in%20question%20and%20choose%20Sort%20by%20color%20and%20sort%20by%20the%20color%20of%20the%20hyperlinks.%20Now%20have%20Excel%20remove%20duplicates.%3C%2FLINGO-BODY%3E
Highlighted
Jessica Burtis
New Contributor

Excel 2016

I have duplicates in my spreadsheet.

 

One duplicate value is hyperlinked, one duplicate value is not. Not all hyperlinks are duplicates.

 

No formulas are used, just data and hyperlinks.

 

I want to remove the duplicates that do not have hyperlinks. Any thoughts?

Thanks!

2 Replies
Solution
When Excel removes duplicates, it leaves the first line in place of each duplicate. So if you sort the table in a smart way so the hyperlinked data is on top, you remove the ones without hyperlinks.
Make sure the filter arrows are visible (Select all data, click the filter icon on the Data tab). Then click the arrow on the column in question and choose Sort by color and sort by the color of the hyperlinks. Now have Excel remove duplicates.
perfect! Each duplicate information column also has a column with dates which change weekly so I just sorted them by date in one column (oldest date first), found and sorted by duplicates and ran the remove duplicates again. Thanks so much!