+ Reply to Thread
Results 1 to 4 of 4

Need to look up and combine data

  1. #1
    Registered User
    Join Date
    08-19-2014
    Location
    DeKalb, IL
    MS-Off Ver
    2013
    Posts
    31

    Need to look up and combine data

    I have a spreadsheet similar to the attached example. Essentially I have the following:

    Table 1: This table is a matrix filled out by a staff. For each area, there are 5 potentially relevant objectives (I say potentially because not all objectives are relevant to each area). I then have a list of documents that would pertain to that area if that particular objective applies. For example, Document A applies to area Blue objective 1 and 2.

    Table 2: I want to automatically pull the values in the highlighted column based on what is populated in table 1. You can see that area Blue objective 1 only pulls document A because that's all that is marked in table 1. Area blue objective 2, however, pulls "A, B" because both A and B are checked in table 1.

    I thought I could try an index and then try to find some way to combine the results but I'm having much more difficulty than I originally thought. I've also found this thread (https://www.mrexcel.com/forum/showth...(Concatenate-)) and it's close, but I'm not quite sure it fits what I need. Any help would be greatly appreciated. Thanks.
    Attached Files Attached Files
    Last edited by sh4d0w1ink; 07-03-2018 at 12:11 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Need to look up and combine data

    Much easier with a user-defined VBA function, int he attached. Note that your use of merged cells meant that the function had to be entered three times and copied to three blocks, rather than entered once and copied down. I have used it both ways to show how to use it correctly.
    Attached Files Attached Files
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    08-19-2014
    Location
    DeKalb, IL
    MS-Off Ver
    2013
    Posts
    31

    Re: Need to look up and combine data

    Interesting, thank you! Do you mind explaining what exactly it's doing? I get the general idea that it's indexing Table 1, but I'm guessing it works differently?

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Need to look up and combine data

    What is is doing exactly is:

    Finding the column needed from the table (1 through 5) based on the value in column C, then looking for x entries (actually, any non-blank value) in that column of the table, checking for the color of interest, and then returning the appropriate letter(s).

    Using spreadsheet functions would have been way too convoluted, if it would even be possible.

+ 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. Combine data from multiple workbooks into one, while combining sheet data
    By NigelKF in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2016, 09:32 PM
  2. Combine Data From Multiple Rows If Data In Column A Is Identical
    By lsilver in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-10-2015, 04:18 PM
  3. Combine Data in Multiple Columns to One Column Based on Data in Rows
    By exceldivx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-09-2014, 07:35 AM
  4. Replies: 8
    Last Post: 11-25-2013, 10:26 PM
  5. Combine data from 4 workbooks and then export filtered data to individual files?
    By womanket in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2013, 12:20 PM
  6. Combine Duplicate Rows and Combine Data in Rows
    By cherylmcgk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2013, 12:04 PM
  7. Using Macros to combine multiple lines of data when Address column data is the same.
    By chrispy123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-23-2012, 01:25 AM

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