Matching numbers separated by commas

[yamamoto]

[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]1, 5, 9, 13, 17, 21, 25, 29
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]2, 6, 10, 14,
18, 22, 26, 30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]3, 7, 11, 15, 19, 23, 27
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]4, 8, 12, 16,
20, 24, 28
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]
Aaron
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Amanda
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

Table 3: Result I want
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Aaron
[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amanda
[/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[/TR]
</tbody>[/TABLE]

I would like a formula to copy to the entire table (table 3) to show day-off of each employee.

First table shows 4 groups of day-offs for employees.
The second table is a lookup of employees who belong to groups in the previous table.
For example Aaron is in the "group 1" so his day-offs are 1st, 5th and 9th of the month, etc.

Can someone help?

 

[Aladin Akyurek]

Why don't we use native function formulas?

1. Select the range that lists ID's and dates together and name the selection DateTable.

2.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][/tr]
[tr][td]

1​

[/td][td]

ID​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][td]

10​

[/td][td]

11​

[/td][td]

12​

[/td][/tr]


[tr][td]

2​

[/td][td]

1​

[/td][td] Aaron[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

3​

[/td][td]

2​

[/td][td] Tom[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

4​

[/td][td]

3​

[/td][td] Amanda[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][/tr]


[tr][td]

5​

[/td][td]

4​

[/td][td] John[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

X​

[/td][/tr]
[/table]

In D2 enter, copy across, and down:

=IF(ISNUMBER(FIND(","&D$1&",",","&SUBSTITUTE(INDEX(DateTable,MATCH($B2,INDEX(DateTable,0,1),0),2)," ","")&",")),"X","O")

Is this what is required?

 

[yamamoto]

Try setting up your data like this:
The UDF can be used in conjuction with other formulas...as shown in the above example.

Thank you so much, mrmmickle1 & Rick. You guys are so helpful. I made a great progress on this project thanks to your help. I may post more questions later as I need to add more conditions.

 

[mrmmickle1]

@Aladin,

Nice work! Great resolution using native functions.

 

[yamamoto]

Hi Aladin,

I missed your post. Thank you for your help. I will try your method when I'm back from my vacation.

Thank you all for being so helpful. I am humbly surprised by this community's kindness.

Bowing,

Y.