+ Reply to Thread
Results 1 to 9 of 9

Find complex name in range and check off matching cells

  1. #1
    Registered User
    Join Date
    07-22-2012
    Location
    The Shire
    MS-Off Ver
    Office 2013
    Posts
    51

    Find complex name in range and check off matching cells

    Hello, everyone!

    I need some help on a matter (example attached, you might just want to skip to it).

    I have a data set that contains some item names and below them the locations, where they are sold (each in a separate cell). So there is this drop-down list with all the items that the user can choose, I want a macro that, when an item is chosen, will search for its name and return the selling locations.

    The layout is that all the possible selling locations are listed for the user in black and only the available ones should be colored red. So far I've made a macro that uses the "Range.Find" method to set a predefined range variable to the item's name cell and then offset it to the start of the selling locations. Then through a "until not isempty (range)" loop (with each loop offsetting one down) match each location listed under the item with the complete list and color the corresponding ones in red.

    I have two problems:

    1. The first one is that some item names are complex, like "Iron / Dark Iron" and that name is contained in a single cell (that's the way it has to be, it cannot be changed) while in the drop-down there are separate entries for "Iron" and "Dark Iron" and each of them must find that one cell. I've gotten somewhere with the "Range.Find" method for simple item names (like "Stove"), but I don't know how to make it work for the complex ones. The "xlPart" won't work, because there are other cells in the range that contain part of the complex names but are not the ones I need.

    2. In the list of selling locations for the user each town is broken down into different shops (so for example there are Oklahoma (East), Oklahoma (North) and Oklahoma (whatever)) but in the data table with the item names they are listen only by towns (so, for example only "Oklahoma") and so my current macro only checks off the first "Oklahoma" and continues down - I don't know how to make it check all "Oklahomas".

    I've also explained it in the example better, I don't know if it is understandable here.

    I haven't included any code, because I thought it would be simpler not to have to mess with my messed-up code. :P

    Unfortunately the layout is not subject to change. :/

    Whew, thank you very much!
    Attached Files Attached Files

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find complex name in range and check off matching cells

    Durarara,

    Attached is a modified version of your posted workbook.
    The first thing I did was create named ranges.

    The named range list_Items is used to make the data validation list in 'Sheet1' cell C3, and refers to:
    Please Login or Register  to view this content.

    Then, I created named ranges whose names are identical to the items that can be chosen.

    So the named range Fence is the list of cities for the item Fence, that named range refers to:
    Please Login or Register  to view this content.
    The named range Wrench is the list of cities for the item Wrench, that named range refers to:
    Please Login or Register  to view this content.

    Lastly I used a Conditional Format formula to turn the appropriate cells red when an item is chose from 'Sheet1' cell C3. Applied to 'Sheet1' cells B5:B19 is this conditional format formula:
    Please Login or Register  to view this content.

    And there you go! Mission accomplished, no VBA required. Does that work for you?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    07-22-2012
    Location
    The Shire
    MS-Off Ver
    Office 2013
    Posts
    51

    Re: Find complex name in range and check off matching cells

    Wow... never even thought of that, actually I didn't even know such a thing was possible.

    If it can work for "Iron" and "Dark Iron" I believe it would work perfectly, yes!

    But I need a little clarification of the conditional formula. As far as I know "INDIRECT" just returns the reference of the pointed cell, how does it return a listed range, that is not defined in the "list_items"? Am I right in assuming that when, let's say, the value "Wrench" is chosen in cell C3 Excel detects that there is also a range with the defined name "Wrench" and links to it, or is there some other sorcery about it?

    And, since I don't completely understand why "SUMPRODUCTS" is used, am I right in assuming that using "SUMPRODUCTS" allows the list of values returned by "INDIRECT" to be treated as an array and therefore each cell, let's say currently B6, to be tested if it contains the city name?

    Thanks all the way!

  4. #4
    Registered User
    Join Date
    11-25-2008
    Location
    Poland
    MS-Off Ver
    MSO 2K3, 2K10
    Posts
    84

    Re: Find complex name in range and check off matching cells

    Durarara,
    This is a sculpture, with the arrangement of the data in Sheet2.
    But somehow I managed with the help of macros.

    In Sheet1 module (not in a standard module eg. Module1)!:
    Please Login or Register  to view this content.
    Artik

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find complex name in range and check off matching cells

    Quote Originally Posted by Durarara View Post
    As far as I know "INDIRECT" just returns the reference of the pointed cell, how does it return a listed range, that is not defined in the "list_items"? Am I right in assuming that when, let's say, the value "Wrench" is chosen in cell C3 Excel detects that there is also a range with the defined name "Wrench" and links to it
    That is exactly correct.


    Quote Originally Posted by Durarara View Post
    am I right in assuming that using "SUMPRODUCTS" allows the list of values returned by "INDIRECT" to be treated as an array and therefore each cell, let's say currently B6, to be tested if it contains the city name?
    Also spot on.

  6. #6
    Registered User
    Join Date
    07-22-2012
    Location
    The Shire
    MS-Off Ver
    Office 2013
    Posts
    51

    Re: Find complex name in range and check off matching cells

    Artik_PL, I'm still trying to understand the entire code, but it searches for a cell with a bold format and from there offsets and so on, right?

    tigeravatar, wow, I'm always amazed how intuitive and "smart" Excel is, but still, can your method work with the "Iron / Dark Iron" thing? That's the main problem.

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find complex name in range and check off matching cells

    Durarara,

    Version 2 working with Stove, Iron, and Dark Iron also. Dark Iron has a space and is therefore an invalid named range reference, so I created the named range DarkIron and made a slight adjustment to the conditional format formula:
    Please Login or Register  to view this content.

    It now substitutes spaces " " with nothing "" so that if C3 contains Dark Iron, it will look at the named range DarkIron. The other named ranges aren't affected because they don't spaces, so nothing is substituted.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-25-2008
    Location
    Poland
    MS-Off Ver
    MSO 2K3, 2K10
    Posts
    84

    Re: Find complex name in range and check off matching cells

    Quote Originally Posted by Durarara View Post
    Artik_PL, I'm still trying to understand the entire code, but it searches for a cell with a bold format and from there offsets and so on, right?
    Yes, the names of items must be bold.
    In this way, the words is not found, such as Iron, Iron Dark (in cell B11 and G11) or Stove(s) in cells B5 or G3. Find method will search for words only in cells with bold text.

    Artik

  9. #9
    Registered User
    Join Date
    07-22-2012
    Location
    The Shire
    MS-Off Ver
    Office 2013
    Posts
    51

    Re: Find complex name in range and check off matching cells

    Wow, now I've understood it all. Thank you very much, both of you!

    Artik_PL, I'm happy that you've shown me something new (for me) in VBA, but I will go with tigeravatar's method, because it seems to me that it is more light and easy for excel to handle.

    Thanks, again!

    This thread is now SOLVED!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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