Remove Duplicates With Unique Values

[Videoman22]

I have a problem recently that is quite challenging.

My spreadsheet has about 54 worksheets. Each worksheet has 4 columns and it organized by a category I picked.

Column A = Url
Column B =Random number
Column C = Website Name

Ideally my goal is to check every single url and make sure they are not duplicated in any other worksheet, but that might not be possible. For now, I'm sticking to checking each worksheet one by one.

I tried conditional formatting and it works great to isolate and color code the duplicates, but there is a problem. Sometimes the url has a / at the end and sometimes it doesn't, so the software will not recognize it.

Example
A B C
https://www.websiteurlhere.com 48965 Website Name
https://www.websiteurlhere.com/ 85794 Website Name

I have no idea how to get Excel to recognize the two url examples above as duplicates and highlight them. I suppose I could attach a / to every cell that doesn't have the symbol, but I don't think that is feasible given the number of urls in each spreadsheet.

Does anybody have any suggestions or recommendations?

Thanks.

[protonLeah]

Software? Current macro?

TestURL =SUBSTITUTE(A1,"/","",3)

[mc84excel]

This is just a left field suggestion. Add formula to Column D to 'clean' the URL value from Column A. Then check Column D for duplicates.

e.g. Cell D2 would be something like

Please Login or Register  to view this content.

You will need to tweak the formula with TRIM if any of your URLs have trailing spaces

[Videoman22]

Where exactly do I put TestURL =SUBSTITUTE(A1,"/","",3) ?

I am not using a macro, but I'm open to ideas.

[Videoman22]

This formula worked great. Thank you.

[mc84excel]

Glad to help. And thank you for the rep

[PCI]

If you had to use a macro to remove the "/" at the end try next code:
It can be included in another one

Please Login or Register  to view this content.