Comparing a list of words within a Range

    Hi,


    I've got a list in "column A" of around 6000 lines which contains key words which I want to extract to "Column b" if It matches but it can match on more than one word .



    e.g


    List
    Six Nations Ireland v England
    FA cup Semi Final Chelsea v Liverpool @ Old Trafford


    Criteria Sample
    Ireland
    England
    Chelsea
    Liverpool


    Ideally I would like to extract the first Town/Country as this is where the event is held unless the @ symbol is used then it's the last Toen/Country as in the FA cup example.


    Thanks in Advance

    Re: Comparing a list of words within a Range



    How do you know to get Ireland, not Six or Nations?

    HTH


    Bob

    Re: Comparing a list of words within a Range


    Hi,


    I'm not sure how you code do that in VBA, but here's a few excel formulas that may help. Basically I've used the find formula to find each name, then used the max & min formulas to work out which team name to show. The final column will produce the "home team". You could do a quick VBA script which copied the formulas down and then hide the columns.


    Richard

    Re: Comparing a list of words within a Range


    Bob,


    The word Ireland would be in the list of criteria. The Code needs to look at the string in Column A and extract the word or words that match the criteria list in Column B


    Thanks VBA noob

    Re: Comparing a list of words within a Range


    Thanks Rico,


    There is a long list of Criteria (150 plus) so not sure how useful this would be.


    VBA Noob

    Re: Comparing a list of words within a Range


    Hi,


    I've had another go at the VBA for this. I've attached the sheet again. I've done everything but the "@" values. I wasn't sure if you wanted the name of the ground, or the away team for them. Basically your criteria list is in column J, and for each value in the list it returns the name in the column beside the search string, as well as where it appears in the string (character number). When it moves to the next value in your search criteria list, it will over-write the previous value if the position of the first character is less than that of the previous entry.


    Hope this makes sense,
    Rico.

    Re: Comparing a list of words within a Range


    Thanks Rico,


    I was after the Venue so would want Old Trafford not Chelsea. Also some cells may only have one location e.g Just the venue name/Area like Cardiff


    Thanks


    VBA Noob

    Re: Comparing a list of words within a Range


    Hi,


    You would need a list of all the places/stadiums to search for I think. This would be your search criteria in column J (ie. Old Trafford would replace Chelsea in the seach list). You could add in a procedure for looking for the place/stadium if the text has an "@" in it. Is it some sort of download from a betting site or something? Could you not amend the way the data is taken into excel? This would probably be easier.


    Rico.

    Re: Comparing a list of words within a Range


    Rico,


    Not for a betting website. Work in the TV Industry and users entry free text with a venue\location or Country in any order so can't do anything with the data. Just trying to do some analysis on how many times we've being to a location\Veune etc.


    Thanks


    VBA Noob

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login