How to find duplicate links in excel documents?

Question

excel

There are folders and excel table files (in the first url column). You need to somehow search through all these files and delete copies of the yurls that are found in these files.
Leave only 1 file.

Answer 1

Macro. Approximate algorithm:
1. Open all files one by one, copy all the information from them to a new sheet
2. Add a new column "file with a duplicate", write the name of the file from where the data was copied from in each cell - in order to know in the future which file you need delete.
2. Apply the formula =COUNTIF(A2:A$1000;A2)-COUNTIF(A:A;A2) in the new column "Double sign", this formula will give a sign of a double (if the data you need is in column A).
Important! For the next line, the formula will look like =COUNTIF(A3:A$1000;A3)-COUNTIF(A:A;A3), for the next one =COUNTIF(A4:A$1000;A4)-COUNTIF(A:A;A4) and so on .d.
Important! In my example, the last cell is A1000, it will be different for you, instead of 1000 put the number of the last filled line.
Example
3. As a result, we will get all duplicates with the number -1, -2, -3, etc., and all unique ones will have a flag of 0.
4. Re-open all files, find duplicates in them through =VLOOKUP or =INDEX(SEARCH ()) and delete them, remembering to check using =IF(AND("file name"=cell of the column "file with duplicate";"Double flag" is less than zero); then delete; otherwise, do nothing).
If there are not many files, then all this can be done manually.

Answer 2

We bring everything into 1 array.
Data-Remove duplicates - select the column with the URL.
We get the result without dancing with a tambourine and a bunch of formulas.
PS This will leave the first unique occurrence of the URL