Matching numbers separated by commas

yamamoto

New Member
Joined
May 25, 2016
Messages
7
[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?
 
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?
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top