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

 

[jtakw]

Yes, that's precisely what my formula does.

As you can see in my sample, which I purposely put "out-of-order" for Column B:

A2 matches B5
A3 matches B4
A4 matches B2
A5 no match
A6 matches B3

Try it on your data and let us know.

 

[John Caines]

Hi Jtakw,

I've managed to get an example for you,, here's a sheet in my dropbox
https://www.dropbox.com/s/kyt9kjjb16yblbc/Redirects.xlsx?dl=0

It has about 90 urls in it,,,
I've highlighted CellA20 in yell,,, this is the OLD URL
The match is found in cell B91,, (The NEW URL

So the output is placed into cell C20 (The combined URLs divided by a colon)

I really hope this clarifies everything Jtakw.

So the formula looks at each cell in Column A,,,, the (Almost) matching URL can be anywhere in Column B,,,, and returns combined URLs seperated by a colon on same Row as original URL, (In this case C20).

Hope this makes in 100% clear,, sorry if it's been a bit misunderstanding here and there.

Many thanks again for all your help
Best regards
JohnC

===========
PS,,, just seen your reply, I will try on data now Jtakw
Many thanks again

 

[John Caines]

Hi Jtakw,

I think you done it!!!!

I'm not great in excel,,,, but I see you used absolute cell references,, (at least I think so,,, in your formula you have B$2:B$5


So when I 1st added the formula it didn't work,,,,
So I saw this,,, and changed to B$2:B$91



And it worked!!!!!
I'm sure this is it Jtakw!!
many thanks for this.

I'll try it out tomorrow.

Hopefully all will be ok.

You really have saved a huge huge headache for me tomorrow.
Many many thanks for this Jtakw.

I really appreciate this.

Have a great evening

best regards
John C

PS... thanks again!!!! Really,,,many thanks

 

[jtakw]

I see you used absolute cell references,, (at least I think so,,, in your formula you have B$2:B$5
So when I 1st added the formula it didn't work,,,,
So I saw this,,, and changed to B$2:B$91

Yes, that's why I mentioned in my Post # 9:

Change/adjust cell references/range as needed:

You need to do this to suit your actual data.

And you're welcome.

If anything needs modified after you test it on your actual data, post back. Let us know.

 

[John Caines]

Hi Jtakw,
Many thanks again,
I think all is good with what you have done here,,, it's great!!
again very much appreciated,,,, saved a huge amount of headache!!

Have a great day
Thanks Jtakw.
Best regards

John C

 

[John Caines]

Hi Jtakw,

Actually just 1 question with your formula if I may..
Your formula now stands at;

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

But I do have 1 slight variation that I wasn't aware of until today
In column A all the URLs are starting with http
But all the URLs that it's now looking for as a match that are in column B are starting with https

This would then alter the formula above I take it?

just wondered if there was any chance of telling me what the formula would look like with this variation?

Sorry about this Jtakw,
I didn't realise this was now the case.

So just to confirm.
All the OLD URLs are in Column A, and start with http

All the NEW URLs that its looking for to match start with https,,,

I hope this makes sense.
Again,, many thanks for your help
Best regards
John C

 

[jtakw]

Formula updated to work for Both URL types (http:// or https://)

Book1
	A	B	C
2	http://www.site.com/awning-rooms/privacy-room-light.html	https://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] : https://www.site.com/test/site1/awning-tables/privacy-room-light.html
4	http://www.site.com/awning-chairs/privacy-room-light.html	https://www.site.com/test/site1/awning-tables/privacy-room-light.html	[url]http://www.site.com/awning-chairs/privacy-room-light.html[/url] : https://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(REPLACE(A2,FIND("/",A2,FIND("//",A2)+2),0,"*"),FIND(":",A2),0,"*"),B$2:B$5),B$2:B$5)

 

[John Caines]

Hi Jtakw,
Many many thanks again,,, that's perfect!!!
Really appreciate this.
It's just what I needed.
Saved the day!

Have a great evening, and thanks again
Best regards
John C

 

[jtakw]

You're welcome.