Excel 2007 : Partial matching cells with locations

[james1911]

Hi,

I tried a lot to find an answer to this but this seems like a tricky probleum.

I have a set of keywords which have locations( something like "jobs in London") in them in column B and set of all locations in the UK (London, Manchester, South East etc) in Column A.

All I want to do is find all the keywords in column B that may have any of the Location terms in Column A. IF I do a partial string match with "Vlookup" it just just shows me the first instance of the location keyword.

It will be good if I can find a formula or macro that will highlight all the keywords in Column B which have any of the locations in Column A.

Thanks in advance for your help

[royUK]

Why not just AutoFilter?

[DonkeyOte]

You could use a Conditional Format - if we assume A1:A10 hold Locations then having highlighted B1:Bn (where n is last row of strings) apply the following formula based Rule:

=LOOKUP(9.99E+307,SEARCH(INDEX(" "&$A$1:$A$10&REPT("ZZZZ",$A$1:$A$10="")&" ",0)," "&$B1&" "))
set format accordingly

The above will then highlight all strings in B that contain one or more locations as listed in A

(the above assumes space delimited strings - modify as necessary)

[james1911]

Hi DonkeyOte

I think this might help. can you please explain some steps. Should i put this formulae in column C

[DonkeyOte]

As outlined the suggestion related to Conditional Formatting.

If you want to put in C you can do though you might want to wrap an ISNUMBER test around the formula - result of TRUE means 1+ locations found.
(without the ISNUMBER test: any number = location found - #N/A means not found)

[james1911]

Thanks for all your help. One last thing can I please have the column number of the string next to the results, just to check which location is in which keyword. Please note that some locations can be in more than 1 keyword

[james1911]

Thanks for changing the title roy. I am not sure how autofilter will help can you please explain in detail.

[james1911]

I am really sorry about this. I am uploading the file here for better clarity

http://www.sendspace.com/file/j26r8b

Keywords driving traffic to ur site are in column A and all Locations are in column B. All we want to do is find all the keywords in Column A which might have any location in Column B. The probleum we face is that during partail match through Isnumber, lookup, Vlookup etc. a keyword is matched with location even if that locations is just a strig "inside that word" ex keyword "Reed" is matched as a location because it exists inside "breedenbroek" though Reed is not a location.

[DonkeyOte]

If you have a file to upload please do so here directly

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.

To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.

Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.

You can then close the window to return to the new post screen.

[james1911]

Here is the file mate. Its Help.xlsx. Kindly have a look

Thanks for your help in advance

[DonkeyOte]

No attachment.

[james1911]

Please find the attached file

[DonkeyOte]

With that much data (>18k locations to search for per keyword and >23k keywords) I would advise against a formula driven solution - is VBA viable ?

[DonkeyOte]

OK - I am looking at this but might not reply until tomorrow.

The code I have presently is still a little on the slow side - ie approx. 40 secs to process just 1000 keywords against the 18k+ locations
Given this code needs to be scalable to handle in excess of 20k keyword phrases this (code/approach) simply isn't viable and requires a re-think on my part.

The ordering of your locations is obviously important and I trust the entries are listed as they are in your sample deliberately ?
(towns, states, countries & continents in that order)

[shg]

I inserted a column for results like so:

       -------------A------------- --B-- --------C--------
   1            Keywords                     Location     
   2   reed.co.uk                        central london   
   3   reed recruitment                  city             
   4   reed jobs                         east london      
   5   reed accountancy                  north london     
   6   reed employment                   north west london
   7   reeds                             south east london
   8   www.reed.co.uk                    south west london
   9   reeds recruitment                 west london      
  10   reed uk                     18014 aberdeen         
  11   reed agency                       aboyne           
  12   reeds jobs                        alford           
  13   reed.com                          ballater         
  14   reed employment agency            ellon            
  15   reed sign in                      fraserburgh      
  16   recruitment                       huntly           
  17   reed specialist recruitment       insch            
  18   reed scientific                   inverurie

Then run this:

Sub x()
    Dim iRow        As Long
    Dim cell        As Range
    Dim av          As Variant
    Dim v           As Variant
    Dim asWd()      As String
    Dim vsWd        As Variant
    Dim i           As Long
    
    With CreateObject("Scripting.Dictionary")
        av = Range("C2", Cells(Rows.Count, "C").End(xlUp)).Value
        iRow = Range("C2").Row

        For Each v In av
            If .Exists(v) Then
                .Item(v) = .Item(v) & Format(iRow, ",0")
            Else
                .Add Key:=v, Item:=CStr(iRow)
            End If
            iRow = iRow + 1
        Next v
        Beep

        av = WorksheetFunction.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value)

        For i = 1 To UBound(av)
            asWd = Split(av(i))
            av(i) = vbNullString

            For Each vsWd In asWd
                If .Exists(vsWd) Then
                    av(i) = av(i) & .Item(vsWd) & ","
                End If
            Next vsWd
            If Len(av(i)) Then av(i) = Left(av(i), Len(av(i)) - 1)
        Next i
    End With

    With Range("B2").Resize(UBound(av))
        .NumberFormat = "@"
        .Value = WorksheetFunction.Transpose(av)
    End With
End Sub

[james1911]

Thanks for having a look.

Actually I too tried a lot of normal excel stuff and could'nt find what I was looking for. VBA should be fine if you can please tell me where that formula goes in VBA exactly. I only know how to get to the homescreen of VBA in excel.

Thanks again. I really appreciate your effort

[james1911]

One last thing SHG, will this Macro work on any number of keywords and locations or there is a limitation. This was a partial list. My complete list has 125,000 keywords and same number of locations. The keywords can have upto 9 words in them. Running this on the complete list gives a type mismatch error and doing a debug points to the following location inside the macro

av = WorksheetFunction.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value)

[shg]

I would not expect there to be a row limitation on the size of an array that can be sucked into memory, but there may certainly be a limitation of absolute array size, and the transpose operation transiently doubles the memory demand (I think; DO, Colin?).

The code could be modified to avoid that, and could be modified to use a Location list on one sheet and multiple keyword lists on other sheets (the names of those lists seems reversed to me).

If you want to put the full Monty on box.net and post a link I'll look at it.

[Colin Legg]

In XL2007 it looks like TRANSPOSE() is limited to 65536 elements. It used to be 5461.

[james1911]

thanks for your help last time mate.

I am facing a very similar probleum again. Actually we added 700 new keywords to our site which in result created 35000 new pages (keyterm & keyterm + location pages). We want to have a look at all our keywords and check if how many of these 700 are driving traffic.

So now we have a list of around 1,00,000 terms that drove traffic to our site last week and we want to find how of our 700 terms that we added can be found (even partially) in those 700.

I tried using the above macro but unfortunaely it works only if these 700 terms are 1 word only and does'nt show any results if they have more than 1 word.