How to map columns of links?

Good morning.

A question.

Will need to do a redirect from one site to another. There are two sitemap.xml from the two sites based on them needs to be done .htaccess with the redirects.

But in these sitemap.XML the links are not in order, and have all selected.

We did Axl file in a single column reference from one sitemap.XML the second column links from the second sitemap.xml

Is there any particular formula, for example: to find the matches on the endings, or match on the last 10 symbols among all the columns and lines? Then it will be easier to find matches among the links
July 2nd 19 at 14:20
2 answers
July 2nd 19 at 14:22
You can do the following: on the side of the plate add 2 columns and put this formula there =of browsing(A2;10), then allocate 2 received column a Home Tab - Conditional formatting Rule to highlight cells - Duplicate values -customizable colors as you like and press OK. Get the result(see Option1).

Note - the formula will return exactly 10 characters to the right. If the link is longer/shorter than 10 characters will be cut is not correct. And may not highlight the result the same links (in red in the screenshot) when the length of the links less than 10 characters, or Vice versa when the length of the links greater than 10 characters to highlight different links (blue in the screenshot in the link above differs 1 character after the / )

More correct to write the formula =prasing(A2;LEN(A2)-SEARCH("/";A2;1)) (assuming that you have a link that is in the format site name/link. (see screenshot Option 2)
Make out the formula from the second option in detail:
Pressing - returns characters from the right edge of a specified length ( the syntax of phrasing("ishex-can be the cell reference";number of characters to return)
LEN(link) - returns the length of the string (number of characters)
SEARCH - returns the number of the desired character in the text (syntax SEARCH("symbol";"cell text"; "the ordinal of the desired symbol"-if you want the 1st 1, if 2nd is 2, etc.)
Therefore if you have links in the format of SITENAME/blah-blah-blah/link in the search instead of 1 put 2.

The second option will highlight the links correctly.

Accordingly also apply conditional formatting as with the first solution. All that is not podsuetilis and there are differences on the links.

If You have links in the format site name/link, and in the format of SITENAME/blah-blah-blah/link write to tell me how to solve this problem.

If something written is not clear is ready to answer questions
July 2nd 19 at 14:24
And why is the last 10 characters? That they match, does not guarantee that there will be false matches.
Cm. the example below, which has two columns with a bunch of hits and a pair of mismatches.
12 line both spicav 2 different addresses, which is the last 10 characters match, and they otravilis as "coincidence". To avoid this, we need to increase or substring, and even better to look at the whole string, as in column H. this column In the MATCH function indicates the index of the row is completely identical to the target in the specified array.
If you have any questions - write here the comments.
This is not very convenient in the sense that it is not arrays and compares 2 cells and it may be due to the different reasons for unloading such that the 1st and 2nd lists of references will be completely different (for example 1st list sorted from A to z, 2nd from z to A). In Your version as in my BUT there are a few. In my Version can be a jamb that the reference appears twice in unloading from the 1st site, and is not found in the unloading from the 2nd website. In my version it is still highlighted as having a match. but you will agree this event is less likely than different levels of sorting. Plus if there is a situation : 1 unloaded reference 1 2 3 4 and 2 in the unloaded references 1 22 2 3 4 and we compare postrock it with the string where the comparison 2 and 22 continue ( 3 and 2 , 4, and 3) we receive the result of the divergence of sootvestvii, but in fact links to matching there - skye.Satterfield72 commented on July 2nd 19 at 14:27
: I mean, compare 2 cells? This is not so. For example, cell H2, the algorithm works like this: "Look, if the range B2:B14 is the cell that contains the address of the cell E2, if there is an exact match, show the number of this row in the array, if not #N/A".
The only restriction here - if in column B there are duplicates, it will show the index of only the first of them. - Adrienne81 commented on July 2nd 19 at 14:30
: Well, or something I do not understand. - Adrienne81 commented on July 2nd 19 at 14:33
: I did not disassemble Your solution to situatii)) I do not understand correctly. and generally, ideally both of them can be combined ( conditional formatting, Your formula) and then still clearer will be the result visually. The truth of the unification decision, I would still be looking for the link in the initial cell search by using the symbol (=praising(A2;LEN(A2)-SEARCH("/";A2;1))) - skye.Satterfield72 commented on July 2nd 19 at 14:36
: Now it's all clear. Yes, conditional formatting is a very handy thing. - Adrienne81 commented on July 2nd 19 at 14:39

Find more questions by tags Excel