+ Reply to Thread
Results 1 to 6 of 6

How to return multiple values to a single cell based on multiple criteria

  1. #1
    Registered User
    Join Date
    05-10-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    How to return multiple values to a single cell based on multiple criteria

    Hello All,

    I have been searching this forum for a solution to my problem for a few days now for. While I've learned some new tricks, I haven't been able to find a solution that meets all of my needs and I'm hoping someone can help. I've attached a sample workbook.

    The information that appears on Sheet2 will actually be contained in a separate file. Sheet1 is a file the client currently uses and populates manually, but I'm trying to get it to populate automatically from Sheet2. I need to return all the days (column C) from Sheet2 that match the Name (A) and Month (B) and populate them in a single cell in Sheet1.

    That is, cell B3 in Sheet1 would display "3,4,5" based on B1 (name) and A2 (month) matching A3:A5 and B3:B5 of Sheet2 respectively. I've found a few examples of INDEX that returns the proper dates, but haven't been able to get all of those days into a single cell.

    I hope this makes sense! Thanks in advance.
    Attached Files Attached Files

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

    Re: How to return multiple values to a single cell based on multiple criteria

    lwallace,

    Welcome to the forum!
    Unfortunately, the CONCATENATE function doesn't work with arrays, so the only viable solution is going to be VBA or an add-in. I recommend using the MoreFunc addin which contains the MCONCAT function which should be able to fit your needs.
    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
    05-10-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to return multiple values to a single cell based on multiple criteria

    Thanks for this. I'm not familiar with VBA or add-ins (my skill level is quite basic in comparison to what is found on this forum); would my client have to download the add-in as well in order for it to work for them? If so, would you know how I could accomplish this with VBA?

    Thanks again.

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

    Re: How to return multiple values to a single cell based on multiple criteria

    Yes, they would have to download the add-in as well. With a VBA solution, they will have to enable macros or the UDF (User Defined Function) won't work.

    Attached is a modified version of your example workbook. Please note that it is now a .xlsm file which means it contains VBA code. When you open it, you will have to enable macros.

    In Sheet1 cell B3 is this UDF formula:
    Please Login or Register  to view this content.

    In cell B4 is this formula (not a UDF, it is a native Excel function in Excel 2007+):
    Please Login or Register  to view this content.

    This is the code used for the UDF:
    Please Login or Register  to view this content.


    How to use a UDF (User Defined Function):
    1. Make a copy of the workbook the UDF will be tested on
      • Always run new code and UDFs on a workbook copy, just in case the code doesn't run smoothly
      • This is especially true of any code that deletes anything
    2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
    3. Insert | Module
    4. Copy the provided code and paste into the module
    5. Close the Visual Basic Editor
    6. In the desired cell, call the UDF like you would a regular formula: =UDFNAME(Argument1, Argument2, etc)
    7. I named this one ConcatIfs
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-10-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to return multiple values to a single cell based on multiple criteria

    That seems to work perfectly. Thank you so much!!

  6. #6
    Registered User
    Join Date
    08-20-2014
    Location
    england
    MS-Off Ver
    2010
    Posts
    1

    Re: How to return multiple values to a single cell based on multiple criteria

    long shot as so long after the origianl post...... i've copied your data into my own spreadsheet and then again with formulas and UDF.... but i just get blank cells on sheet1?

    the end product i'm after (i'm sure is the same as yours) is i have a spreadsheet1 that has names, skills, Age and department these could be multiples ie
    Name J Smith Skill 1 Dept A Age 25
    Name B Smith Skill 1 Dept A Age 30
    Name J Smith Skill 4 Dept B Age 25

    I need a formula or UDF or VBA that would return Name and Age based on the criteria of Dept and Skill. For example:
    Dept A Skill 1 would return IN ONE CELL : J Smith 25, B Smith 30

    Thanks in advance!

+ 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