Put a list of cell data in a single cell based off of specific values

Posted on

QUESTION :

I know the question title isn’t great, I can’t think of a good way to phrase the question, making hard for me to find answers just searching around online. I don’t have a ton of experience in excel, but I am usually able to figure out how to do something with a couple quick google searches.

So far I’ve tried multiple combination of the functions TEXTJOIN(), FILTER(), SEARCH() and FIND().

I’m tryin to make a schedule based off of free time.

I used google forms to poll when people are free what days (Sheet 1).

Sheet 1 (Data)                         
    A      B         C
1  NAME   MON       TUES      
2  Dan    1, 2, 5   1, 4
3  Robert 12, 3     12, 3
4  Gregg  1, 2      4,
5  Joe    2, 3      3,

I want to be able to see the overlap of free time so I can create a schedule around when two people are free at once.

Sheet 2 (Results)
     A        B                  C   
1   TIME     MON                TUES
2    12      Robert             Robert 
3    1       Dan, Gregg         Dan
4    2       Dan, Gregg, Joe    
5    3       Robert, Joe        Robert, Joe 
6    4                          Dan, Gregg
7    5       Dan                

ANSWER :

[Edit ] added screenshots, alternate delineation correction, an additional caveat.

This works.

=TEXTJOIN(", ", TRUE, IF(IFERROR(FIND(","&TRIM($A2)&",",","&SUBSTITUTE(Sheet1!B:B," ","")&","),),TRIM(Sheet1!$A:$A),""))

Enter in cell B2 then CtrlShiftEnter (CSE) to make it an array formula. Copy drag across and down.

The day cells with the hours when free, must be comma delimited just like the sample data.

  • Anywhere between the numbers is fine: 12, 1 ,2.

Shortcut to Matching Delimited values in a String
Before the find function uses the values of the Sheet-2, hour cell, and the Sheet-1, hours cell, this formula strips out the spaces and adds commas to both the right and left side of the input from these cells. Then find looks for a number bracketed by commas in a string with no spaces; just numbers and commas with commas at both ends of both arguments.

  • Find( ",2," , ",12,1,2," ).
    Note the commas bracketing both ends of the search for value and the search in value.

[Edit] NB This formula does not match Day columns. It uses the relative locations. If the sequence order between the data Mon | Tue | Wed and the results Mon | Wed differs, then each result column formula will need to be edited to align the Sheet1!DayColumn reference correctly (or customize the formula further to automatically match the columns by header).

Delineation Corrections

The above formula concatenates numbers when they are separated by space and there is not a comma in between them 1 212.

Gregg is added to 12 on Tues because the space between the 1 and the 2 is removed by the formula (Gregg is not added to 1 nor 2). Joe would be placed on Tues at 14 if this were a 24 hundred clock (Joe is not added to 1 nor 4).

Alternate delineation correction. Replace spaces with commas 1 21,2.

=TEXTJOIN(", ", TRUE, IF(IFERROR(FIND(","&TRIM($A2)&",",","&SUBSTITUTE(Sheet1!B:B," ",",")&","),),TRIM(Sheet1!$A:$A),""))

Gregg is added to 1 and 2 on Tues because the space between the 1 and the 2 is replaced with a comma (Gregg is not added to 12). Joe is added to 1 and 4.

If neither delineation correction is acceptable, then create a data integrity formula and:

  • add the integrity formula to the result formula
  • put the integrity formula into a notification column
  • use the integrity formula to conditionally format (highlite) the data and/or the results

Leave a Reply

Your email address will not be published. Required fields are marked *