excel vlookup partial match for URLs

Hi All,

I hope someone can help with this, and I will try to be as detailed as possible.

I have a list of website URL’s I have removed the Http:// addresses from each of the cells. So my data looks like the below list

Support/
Support/Consumer_Products/index.aspx
Support/Consumer_Products/operating_system_drivers/
Support/business-product-support/
Support/Consumer_Products/products/printers/
Support/Consumer_Products/where_to_find_your_serial_number/
Support/Consumer_Products/products/Fax__Multifunctionals/
Support/Consumer_Products/services_locator/
ect

What I’m trying to do is partial match to group a bunch of URLS together for for example

2014-12-08 15_30_06-Microsoft Excel - Entry Page Lookup List.xlsx.png



For for example the following URLS would be grouped into “Support - Driver Download” using the above look-up table.
Support/Driver_Download/system/
Support/Driver_Download/cameras/

And if the following URLS are found then these would be grouped into “Support - Consumer Products - Products - Fax & Multifunctionals”
Support/Consumer_Products/products/Fax__Multifunctionals/productA (there are over a 100 product names)
Support/Consumer_Products/products/Fax__Multifunctionals/productB
Support/Consumer_Products/products/Fax__Multifunctionals/productC

Hope you get the idea. I'm trying to get my Vlookup to match part of the string and return the closest group it can find in the lookup table. I have tried using wildcards etc, but I cannot seem to get anything to work

Thanks

Create a helper column in col. B..
Assuming your list of trimmed URL's starts in A2, place this into B2 and copy down.
=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"/","|",1))+1,LEN(A2)),"/",REPT(" ",LEN(A2))),LEN(A2)))

Sort by this column and you'll have your groups.
HTH.

Pete

Hi

It's probably best to calculate the summary group from the string by identifying the last occurrence of "/".

1 Count the number of "/" in a string. LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))
2 Find the position of the final "/" in the string using the following UDF.

Function FindN(sFindWhat As String, _
sInputString As String, N As Integer) As Integer
Dim J As Integer

Application.Volatile
FindN = 0
For J = 1 To N
FindN = InStr(FindN + 1, sInputString, sFindWhat)
If FindN = 0 Then Exit For
Next
End Function

From http://excel.tips.net/T003324_Findin...Character.html

3) The group will be =LEFT(B1,FINDN-1)

Thanks, Im not too sure if I understand you corretly (bit advacned for me). below is what I have been using, it works for some cells but not all.

=VLOOKUP("*"&B2&"*",'[Entry Page Lookup List.xlsx]Sheet2'!$A$2:$B$40,2,FALSE)

Thanks