**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 `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).

### Delineation Corrections

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

→ `12`

.

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 2`

→ `1,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