Matching data from 2 columns to show in 1 output

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,123
Office Version
  1. 2019
Platform
  1. Windows
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

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

New URL

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

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,123
Office Version
  1. 2019
Platform
  1. Windows
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)
Code:
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;
Code:
 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

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
Could the Domain Name (Site.com)of an Old Site DIFFER from the Domain Name (Site.com) of the New Site?
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,123
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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...
Code:
 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

Well-known Member
Joined
Aug 28, 2006
Messages
1,123
Office Version
  1. 2019
Platform
  1. Windows
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
Code:
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

Ok, thanks for clarifying...

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

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,123
Office Version
  1. 2019
Platform
  1. Windows
====================
PS,,,, sorry jtakw,, I've just re-read your question,,,

If there is no match at all,,,,
Code:
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
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">http://www.site.com/awning-rooms/privacy-room-light.html</td><td style=";">http://www.site.com/test3/site3/awning-chairs/privacy-room-light.html</td><td style=";">http://www.site.com/awning-rooms/privacy-room-light.html : http://www.site.com/test/site/awning-rooms/privacy-room-light.html</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">http://www.site.com/awning-tables/privacy-room-light.html</td><td style=";">http://www.site.com/test1/site/living-rooms/privacy-room-light.html</td><td style=";">http://www.site.com/awning-tables/privacy-room-light.html : http://www.site.com/test/site1/awning-tables/privacy-room-light.html</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">http://www.site.com/awning-chairs/privacy-room-light.html</td><td style=";">http://www.site.com/test/site1/awning-tables/privacy-room-light.html</td><td style=";">http://www.site.com/awning-chairs/privacy-room-light.html : http://www.site.com/test3/site3/awning-chairs/privacy-room-light.html</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">http://www.site.com/bed-rooms/privacy-room-light.html</td><td style=";">http://www.site.com/test/site/awning-rooms/privacy-room-light.html</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">http://www.site.com/living-rooms/privacy-room-light.html</td><td style="text-align: right;;"></td><td style=";">http://www.site.com/living-rooms/privacy-room-light.html : http://www.site.com/test1/site/living-rooms/privacy-room-light.html</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet582</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=A2&" : "&LOOKUP(<font color="Blue">2,1/SEARCH(<font color="Red">REPLACE(<font color="Green">A2,FIND(<font color="Purple">"/",A2,FIND(<font color="Teal">"//",A2</font>)+2</font>),0,"*"</font>),B$2:B$5</font>),B$2:B$5</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down.
 

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,123
Office Version
  1. 2019
Platform
  1. Windows
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
Code:
 http://www.site.com/awning-rooms/privacy-room-light.html

The match which is in Column B,, which is;
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top