Matching data from 2 columns to show in 1 output

[John Caines]

Hi All,

I need an output that displays the URL in column A (Old URL) that most closely matches the URL from column B (New Url)

The product name & code number stay the same in both A & B there are just some extra categories in the column B list

The output needs to be

Something like Column A (Old URL) : Column B (New URL)

I was thinking this could be done via some sort of Vlook up, but I am not sure how to make it work

As an example the

Old URL

 http://www.site.com/awning-rooms/privacy-room-light.html

New URL

 http://www.site.com/test/site/awning-rooms/privacy-room-light.html

 

[John Caines]

Sorry, just to add,

there's about 20,000 of these URLs!! .
It's actually for some URL re-directs.

Just to clarify, I'm trying to get a formula that look at all the OLD URLs In Column A (About 20,000!) and then look at all the NEW URLs in column B,,,
& if there is an exact match (except for)

test/site/

Which we will call 2 extra categories (that could be called anything, not just test and site)
Then it would output the following;

 http://www.site.com/awning-rooms/privacy-room-light.html :  http://www.site.com/awning-rooms/privacy-room-light.html

As the above is an exact match.

The problem I have also is that the new URLs in Column be are in no particular sorted order I'm afraid.

Is this a vlookup formula?
I really don't have a scoobies.
If anyone can help me out here I'd be really grateful.

---------
So the logic is; (I guess)
"Whatever is in Column A from cell A2, Please look for it in entire Column B. If exact match (barr the 2 extra categories) is found Output these matching OLD and NEW URLs together (seperated by a colon) In Column C (Cell C2)
---------

I hope all the above makes sense.
Really save my bacon if someone can help me out with this.

Just to add,,, a formula could go all the way down into Column C if easier, then the matching output in column D

Many thanks ,
Again, really hope someone can work this out and it all makes sense
Best regards
John C

 

[jim may]

Could the Domain Name (Site.com)of an Old Site DIFFER from the Domain Name (Site.com) of the New Site?

 

[jtakw]

Hi,

Also, what output do you want when there's No Exact match ?

 

[John Caines]

Hi Jim!!
Many thanks for your reply!!
No,,
Domain is exactly the same,,,,
It's all for the same site,,,,

It's just the Column A list (OLD Site) we obviously have,
But someone has made a whole new website,, with some new URLs added etc,,,, but the new list (Column B URLS) is based on the OLD websites URLs,,, but they have 2 added categories (As in example above, test/site/)

But the new list has been given to us in no particular order.

Hope this makes sense. So we somehow have to try and match them up,,,

Just to add,,, typo in above post 2,,, the OUTPUT code should have been...

 http://www.site.com/awning-rooms/privacy-room-light.html :   http://www.site.com/test/site/awning-rooms/privacy-room-light.html

So OLD URL 1st,,, then the match that has been found 2nd separated by a colon

I really hope it makes sense to you Jim.
Many thanks for getting back to me
Best regards
John C

 

[John Caines]

Sorry jtakw,
just seen your reply,,,

Output is like I've just mentioned above to Jim's reply. Sorry if I've kind of worded it wrong with saying EXACT MATCH

Here's example again jtakw

http://www.site.com/awning-rooms/privacy-room-light.html : http://www.site.com/test/site/awning-rooms/privacy-room-light.html

So,,, 1st URL is Column A,,, then,, if formula finds matching url (Without the 2 categories that are placed just after main domain IE in example above http://www.site.com,,,,, it returns this combined in column C (or D if easier ,,, as the formula might want to go in column C)

Hope this makes sense jtakw.

Many thanks for your reply
Best regards
John C

 

[jtakw]

Ok, thanks for clarifying...

Just in case, what Output would you want when there's No match at all ?

 

[John Caines]

====================
PS,,,, sorry jtakw,, I've just re-read your question,,,

If there is no match at all,,,,

N/A

I guess,,,

Not too sure,,, but there shouldntr be too many no matches,, I only think 30 new pages were built from what I heard,,, so no worries if it's just N/A

best regards
JohnC

 

[jtakw]

Ok, try this with your data, as the formula is, it will produce a #N/A error when there's No match, we can add an IFERROR to change the No match output to whatever you want (i.e. "No Match", "Nada", "" Blank, etc.) if you'd prefer.

Change/adjust cell references/range as needed:

Book1
	A	B	C
2	http://www.site.com/awning-rooms/privacy-room-light.html	http://www.site.com/test3/site3/awning-chairs/privacy-room-light.html	[url]http://www.site.com/awning-rooms/privacy-room-light.html[/url] : http://www.site.com/test/site/awning-rooms/privacy-room-light.html
3	http://www.site.com/awning-tables/privacy-room-light.html	http://www.site.com/test1/site/living-rooms/privacy-room-light.html	[url]http://www.site.com/awning-tables/privacy-room-light.html[/url] : http://www.site.com/test/site1/awning-tables/privacy-room-light.html
4	http://www.site.com/awning-chairs/privacy-room-light.html	http://www.site.com/test/site1/awning-tables/privacy-room-light.html	[url]http://www.site.com/awning-chairs/privacy-room-light.html[/url] : http://www.site.com/test3/site3/awning-chairs/privacy-room-light.html
5	http://www.site.com/bed-rooms/privacy-room-light.html	http://www.site.com/test/site/awning-rooms/privacy-room-light.html	#N/A
6	http://www.site.com/living-rooms/privacy-room-light.html		[url]http://www.site.com/living-rooms/privacy-room-light.html[/url] : http://www.site.com/test1/site/living-rooms/privacy-room-light.html
Sheet582

Cell Formulas
Range	Formula
C2	=A2&" : "&LOOKUP(2,1/SEARCH(REPLACE(A2,FIND("/",A2,FIND("//",A2)+2),0,"*"),B$2:B$5),B$2:B$5)

Formula copied down.

 

[John Caines]

Many many thanks for your reply jtakw.

Same I don't have the URL list with me now,
But just to confirm your formula above,,,,

I just want to make 100% sure I explained it right.

If this URL is in Column A from the (OLD List),, lets say cell A2

 http://www.site.com/awning-rooms/privacy-room-light.html

The match which is in Column B,, which is;

http://www.site.com/test/site/awning-rooms/privacy-room-light.html

This could be anywhere in Column B,,, IE it could be in cell B1250

And if the formula finds it,,, it returns the following into cell C2

http://www.site.com/awning-rooms/privacy-room-light.html : http://www.site.com/test/site/awning-rooms/privacy-room-light.html

If you can just confirm if your code does this jtakw?
It's just that every URL in Column A,,, will have a match somewhere in Column B,,,, but the almost certainly aren't on the same row,,, as what's been sent us is a new list of URLs in no particular order.

I really hope this makes sense.
And if you can confirm your formula will find the match from any cell in Column B?

Many thanks for your help here jtakw.

Very very much apreciated.

Many thanks
JohnC