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
[Edit ] added screenshots, alternate delineation correction, an additional caveat.
=TEXTJOIN(", ", TRUE, IF(IFERROR(FIND(","&TRIM($A2)&",",","&SUBSTITUTE(Sheet1!B:B," ","")&","),),TRIM(Sheet1!$A:$A),""))
Enter in cell
B2 then Ctrl–Shift–Enter (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).
The above formula concatenates numbers when they are separated by space and there is not a comma in between them
1 2 →
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
2). Joe would be placed on Tues at
14 if this were a 24 hundred clock (Joe is not added to
Alternate delineation correction. Replace spaces with commas
1 2 →
=TEXTJOIN(", ", TRUE, IF(IFERROR(FIND(","&TRIM($A2)&",",","&SUBSTITUTE(Sheet1!B:B," ",",")&","),),TRIM(Sheet1!$A:$A),""))
Gregg is added to
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
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