+ Reply to Thread
Results 1 to 13 of 13

Dynamic Array lookup

  1. #1
    Registered User
    Join Date
    09-18-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    5

    Dynamic Array lookup

    Bottom Line: I have a look-up list set that needs to reference information in a "sentence" to dynamically insert a result value (see example of data below), and I need to figure out a way to run a job over several lines int eh look-up table:

    EX:
    Phrases Assigned to Keyword Assigned to
    the ocean is blue blue Joe
    I like his blue shirt red Bob


    The keyword/Assigned to columns are my vlookup (I believe) and I need to search rthe data in "Phrases" to distill the color, so I can add the "Assigned to" information.

    the rules for this "test" are below:

    The data in column A contains a series of phrases. Every phrase contains one color in the phrase. The goal of the challenge is to use the lookup table in D2:E10 to assign the phrase to one of the names in column E.
    You can use a formula or a macro. The solution should be adaptable to any size data in column A and any length table in columns D:E. Yes, someone with Excel 2007 could nest 8 IF statements to solve the current problem, but this will not extend to a table with 34 entries, so this is not a valid approach.

    I have attached the file - the correct tab is "Challenge 2"
    Attached Files Attached Files
    Last edited by lopg2009; 09-18-2010 at 01:33 PM. Reason: Solved!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Dynamic Array lookup

    Is this your homework? :-)

    Regards

  3. #3
    Registered User
    Join Date
    09-18-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Dynamic Array lookup

    yep. Fun stuff, but something I need to learn and understand

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Dynamic Array lookup

    Are you looking for a simple loop?

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-18-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Dynamic Array lookup

    OK - Since this is all macro/vba, can you give me a couple of things?

    1: what does this mean in "simple english"
    2: does this fit the dynamics of the challenge? (meaning scalable to large data sets)
    3: is this just a basic macro that i run and it will auto populate the results?

    Thanks so much for the quick response and your help.

  6. #6
    Registered User
    Join Date
    09-18-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Dynamic Array lookup

    One last thing....

    I need the Name result to be entered in to the blank "Assigned to" column (B) next tot he "color" phrase.

    So where your script worked to REPLACE the color witht he name, I need the phrase untouched, but the Assigned to column populated.

    The script you wrote was REALLY cool, though.

    Thanks!

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic Array lookup

    Hi,

    One macro way.
    Name the cell A5 "phrase_top", and "D5" "keyword_top" and delete your comments on rows 1 & 2.

    Add the following procedure in the VBE and run it.

    Please Login or Register  to view this content.
    The file is attached.

    HTH
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  8. #8
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Dynamic Array lookup

    Oh Yes ,
    Please Login or Register  to view this content.

  9. #9
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Dynamic Array lookup

    I'd use UDF way. it's much simpler, IMO...

    Please Login or Register  to view this content.
    How to use:
    1. Copy above code.
    2. In Excel press Alt + F11 to enter the VBE.
    3. Press Ctrl + R to show the Project Explorer.
    4. Choose Insert -> Module.
    5. Paste code into the right pane.
    6. Press Alt + Q to close the VBE.
    7. Save workbook before any other changes.

    Then in B5 use this formula and copy down

    =VLOOKUP(SUBSTITUTE(SUBSTITUTE(MID(A5,FINDSEVERAL($D$5:$D$13,A5),FIND(" ",MID(A5,FINDSEVERAL($D$5:$D$13,A5),255)&" "))," ",""),CHAR(160),""),$D$5:$E$13,2,0)

    PS: Workbook is also attached.

    This funny enough, but teacher may ask you "How this works". And you will have to explain that.

    Start explaining from FINDSEVERAL function. This function searches for several words (like in Keyword column) within one word and returns integer number, indicating starting position of look-up value. Macro, loops for given words, and assign numeric value to FINDSEVERAL variable.

    You will have to crop this word starting from the position returned by FINDSEVERAL function.
    MID(A5,FINDSEVERAL($D$5:$D$13,A5),255)&" ")

    For example this function will return orange shirt from I like his orange shirt

    Now you've got your color as first word in a sentence. The only thing left to do, is to find first dash after color, and extract that it. Below function does that.

    MID(A5,FINDSEVERAL($D$5:$D$13,A5),FIND(" ",MID(A5,FINDSEVERAL($D$5:$D$13,A5),255)&" "))

    This function will return orange from orange shirt

    Now you've extracted your color. But gotta say you teacher is very sly. He used to two type of dashes: simple dash, another ones CODE(160). Nested SUBSTITUTE functions were used here to get rid of all useless dashes after a color. For example in some places above function returns "red", in some places "red ". These dashes can be either a simple dashes, typed by "spacebar" button on the keyboard, or 160s symbol of ANSI table.

    After getting the color w/o any leading or trailing dashes or any useless symbols, VLOOKOP formula, will lookup-up that value within range D5:E13, and will return value from second column of that range

    Hope you'll get the highest mark))))
    Attached Files Attached Files
    Last edited by contaminated; 09-18-2010 at 01:27 PM.
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Dynamic Array lookup

    You can do this with a User Defined Function

    Please Login or Register  to view this content.

    A short subroutine to test the function

    Please Login or Register  to view this content.

    In your worksheet, use it as; =fColourAssign(A5)


    Note that this does not work with some of your data ... where the colour is the last word in the phrase. It seems as though there are some non printing characters that TRIM does not remove.

    Regards
    Last edited by TMS; 09-18-2010 at 01:28 PM.

  11. #11
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Dynamic Array lookup

    I've edited Post # 9. Please take a look.

  12. #12
    Registered User
    Join Date
    09-18-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Dynamic Array lookup

    Everyone - Thanks a TON!!!

  13. #13
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Dynamic Array lookup

    -------------Deleted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1