+ Reply to Thread
Results 1 to 4 of 4

Identify values in a cell based from another sheet and concatenate the results in one cell

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    95

    Exclamation Identify values in a cell based from another sheet and concatenate the results in one cell

    In the attached excel file, first row is the sample output. 3rd column has all the states separated by comma and each state has a corresponding region. I need to be able to identify regions of each state and put it in the regions column. if more than 1 state is in the same region, 'Northwest' for example, just put 'Northwest'.

    I hope I make sense.

    Thanks guys!
    Attached Files Attached Files
    Last edited by danallamas; 06-15-2015 at 12:35 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Identify values in a cell based from another sheet and concatenate the results in one

    I've written a UDF for you to use for this and installed it into your sample workbook.

    It's used in a cell like so:

    =ListRegions(Reference, ListOfStateAbbreviations, ListOfRegions, OptionalDelimiter)
    =ListRegions(B2, 'Region Database'!B:B, 'Region Database'!F:F, ", ")

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-02-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: Identify values in a cell based from another sheet and concatenate the results in one

    This is so perfect thank you so much!

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Identify values in a cell based from another sheet and concatenate the results in one

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Concatenate a Range based on cell values
    By nuruedriss in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 01:03 PM
  2. Concatenate multiple cell values based on matching adjacent cells
    By mkrzy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2013, 02:40 AM
  3. [SOLVED] Search a range for cells that have values and set value in another cell based on results
    By Catsup in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-25-2013, 04:57 PM
  4. [SOLVED] Concatenate cell values based on condition
    By alexandruc in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-06-2012, 07:01 AM
  5. concatenate cell values based on row number
    By ESeufert in forum Excel General
    Replies: 1
    Last Post: 07-02-2007, 02:45 PM

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