+ Reply to Thread
Results 1 to 4 of 4

Using CSV cell values to reference a table and return matches in CSV format in new cell

  1. #1
    Registered User
    Join Date
    04-24-2015
    Location
    Spencer, IA
    MS-Off Ver
    Office 2010
    Posts
    13

    Using CSV cell values to reference a table and return matches in CSV format in new cell

    So I have column A for the staff number which matches row number and the number then corresponds to the staff member's name in column B.

    So it looks a little like this but with 26 staff members currently:

    1 John
    2 Jane
    3 Steve
    4 Allison

    Now in another sheet I'll have the day of the week in column A and the employee numbers in column B, but there actually could be more than 4 employees working a certain day. There could be 10, but it looks like so:

    Mon 1,2,3
    Tue 2,3,4
    Wed 1,2,4
    Thur 1,3,4
    Fri 1,2,3,4
    Sat 2,4
    Sun 1,3

    Then in column C I am trying to write a function that checks each number in column B and displays the corresponding names separated by the commas so the final should look like this:

    Mon 1,2,3 John,Jane,Steve
    Tue 2,3,4 Jane,Steve,Allison
    Wed 1,2,4 John,Jane,Allison
    Etc...

    I feel like it could be entirely possible, but I am just not good enough at this yet. Thank you in advance for your time on attempting to help me on this, I greatly appreciate it!

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,572

    Re: Using CSV cell values to reference a table and return matches in CSV format in new cel

    This proposed solutions employs 20 helper columns, XEK:XFD, on sheet2.
    The formula for column XEU is: =LEFT(B2,SEARCH(",",B2)-1)
    The formula for columns XEK:XET is: =IF(XEK2="","",IFERROR(MID($B2,SEARCH(",",$B2,SEARCH(XEK2,$B2))+1,SEARCH(",",$B2,SEARCH(XEK2,$B2)+1)-SEARCH(XEK2,$B2)),""))
    The formula for columns XEU:XFC is: =IFERROR(INDEX(Sheet1!$B$1:$B$12,MATCH(VALUE(XEK2),Sheet1!$A$1:$A$12,0))&IF(XEL2<>"",",",""),"")
    The formula for columns XFD is: =IFERROR(INDEX(Sheet1!$B$1:$B$12,MATCH(VALUE(XET2),Sheet1!$A$1:$A$12,0)),"")
    The formula for column C is: =CONCATENATE(XEU2,XEV2,XEW2,XEX2,XEY2,XEZ2,XFA2,XFB2,XFC2,XFD2)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    04-24-2015
    Location
    Spencer, IA
    MS-Off Ver
    Office 2010
    Posts
    13

    Re: Using CSV cell values to reference a table and return matches in CSV format in new cel

    I literally got a similar version to work 5 minutes before you posted, but your way is much more efficient and faster than mine. I had some fairly bulky functions to locate the commas I was looking for in my helper columns. I will be changing to your setup to speed my sheet up! Thank you!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,572

    Re: Using CSV cell values to reference a table and return matches in CSV format in new cel

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ 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. [SOLVED] Populating a table on another sheet - reference 1 cell, return another
    By tweaver in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-13-2016, 12:20 PM
  2. [SOLVED] Return value when part of a cell matches a word in a reference column
    By crazyb78 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 07-29-2015, 05:28 AM
  3. [SOLVED] Vlookup or If/Then to return a substitute cell for a cell that matches itself in a range
    By Shadefalcon in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-13-2012, 06:02 PM
  4. Replies: 3
    Last Post: 05-09-2012, 06:22 PM
  5. [SOLVED] UDF to return string of values of all rows with cell matching reference cell
    By oemgacaesar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2012, 12:43 PM
  6. Custom Cell Format - Any Reference Table ?
    By jeremymc7 in forum Excel General
    Replies: 1
    Last Post: 07-29-2008, 02:21 PM
  7. [SOLVED] return cell reference in a table based upon given lookup criteria
    By Travis in forum Excel General
    Replies: 3
    Last Post: 03-15-2006, 10:35 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