How to quickly map link columns?

Question

excel

Good morning.
A question.
You will need to redirect from one site to another. There are two sitemap.xml from two sites, on their basis it is required to make .htaccess with redirects.
But in these sitemap.XML links are out of order, and you have to manually select everything.
We made an axel file, in one column of links from one sitemap.XML in the second column of links from the second sitemap.xml
Is there any formula, for example: to find matches by endings, or matches by the last 10 characters among all columns and rows? Then it will be easier to find matches among links

Answer 1

You can do the following: add 2 columns to the side of the plate and write the formula there = RIGHT (A2; 10) , then select 2 received columns - Home tab - Conditional formatting - Cell selection rule - Repeating values ​​- set the color as you like and click OK. The result will be obtained (see screenshot Option 1).
Please note - the formula will return exactly 10 characters from the right. If the link is longer/shorter than 10 characters, it will not be cut correctly. And the result of identical links may not be highlighted (in red in the screenshot) with a link length of less than 10 characters, or vice versa, with a link length of more than 10 characters, highlight different links (in the screenshot in blue - 1 character after / is different in the links )
It would be more correct to write the formula=RIGHT(A2;DLSTR(A2)-SEARCH("/";A2;1)) (provided that your link is in the format site_name/link. (See screenshot Option2)
I will analyze the formula from the second option in detail:
RIGHT - returns characters from the right edge of the specified length ( syntax RIGHT("out_text-can be a reference to a cell";Number of characters returned)
LLSTR (reference) - returns the length of the string (number of characters)
SEARCH - returns the ordinal number of the searched character in the text ( syntax SEARCH ("character"; "cell with text"; "serial number of the desired character" - if you need the 1st then 1, if the 2nd then 2, etc.)
Therefore, if you have links in the format site_name/bla -bla-bla / link then in the search, instead of 1, put 2.
The second option will highlight the links correctly.
Accordingly, you also apply conditional formatting as with the first solution. Everything that is not highlighted and there are discrepancies in the links.
If you have links both in the format of site_name / link, and in the format of site_name / blah blah blah / link, write a hint how to solve such a problem.
:-)
If something written is not clear, I am ready to answer questions

Answer 2

Why exactly the last 10 characters? Just because they matched doesn't guarantee that there won't be false matches.
See the example below, which has two columns with a bunch of matches and a couple of mismatches.
In the 12th line of both lists there are 2 different addresses, in which the last 10 characters matched, and they were caught as a "coincidence". To avoid this, you need to either increase the substring, or even better, search the entire string, as in column H. In this column, the MATCH function shows the index of the string that is completely identical to the desired one in the specified array.
If you have any questions - write comments here.