Matching numbers separated by commas

Y

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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
yamamoto,

Welcome to the messageboard!


What about using a UDF to solve your issue:
Code: 
=StringOutX(YourCommaDelimitedString, YourCriteriaString)


Code: 
[COLOR=#0000ff]Function[/COLOR] StringOutX(Commastr [COLOR=#0000ff]As String[/COLOR], Refstr [COLOR=#0000ff]As String[/COLOR])


  [COLOR=#0000ff]  Dim[/COLOR] myInt [COLOR=#0000ff]As Integer[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] myVarCount [COLOR=#0000ff]As Integer[/COLOR]
    
    myVarCount = Len(Commastr) - Len(Replace(Commastr, ",", ""))
    
[COLOR=#0000ff]    For[/COLOR] myInt = 0[COLOR=#0000ff] To[/COLOR] myVarCount
    
      [COLOR=#0000ff]  If [/COLOR]Trim(Split(Commastr, ",")(myInt)) = Refstr[COLOR=#0000ff] Then[/COLOR]
            StringOutX = "X"
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Next[/COLOR] myInt


[COLOR=#0000ff]End Function[/COLOR]



Excel 2010
ABCDEFGHIJKLMNO
1123456789101112
21, 5, 9, 13, 17, 21, 25, 29AaronX000X000X000
32, 6, 10, 14,Tom0X000X000X00
43, 7, 11, 15, 19, 23, 27Amanda00X000X000X0
54, 8, 12, 16,John000X000X000X
Sheet2
Cell Formulas
RangeFormula
D2=StringOutX($A$2,D$1)
 
Upvote 0
mrmmickle1 said:
yamamoto,

Welcome to the messageboard!


What about using a UDF to solve your issue:
Code: 
=StringOutX(YourCommaDelimitedString, YourCriteriaString)


Code: 
[COLOR=#0000ff]Function[/COLOR] StringOutX(Commastr [COLOR=#0000ff]As String[/COLOR], Refstr [COLOR=#0000ff]As String[/COLOR])


  [COLOR=#0000ff]  Dim[/COLOR] myInt [COLOR=#0000ff]As Integer[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] myVarCount [COLOR=#0000ff]As Integer[/COLOR]
    
    myVarCount = Len(Commastr) - Len(Replace(Commastr, ",", ""))
    
[COLOR=#0000ff]    For[/COLOR] myInt = 0[COLOR=#0000ff] To[/COLOR] myVarCount
    
      [COLOR=#0000ff]  If [/COLOR]Trim(Split(Commastr, ",")(myInt)) = Refstr[COLOR=#0000ff] Then[/COLOR]
            StringOutX = "X"
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Next[/COLOR] myInt


[COLOR=#0000ff]End Function[/COLOR]



Excel 2010
ABCDEFGHIJKLMNO
1123456789101112
21, 5, 9, 13, 17, 21, 25, 29AaronX000X000X000
32, 6, 10, 14,Tom0X000X000X00
43, 7, 11, 15, 19, 23, 27Amanda00X000X000X0
54, 8, 12, 16,John000X000X000X
Sheet2
Cell Formulas
RangeFormula
D2=StringOutX($A$2,D$1)
Click to expand...
You can also write that UDF as a one-liner...
Code: 
Function StringOutX(Commastr As String, Refstr As Variant)
  StringOutX = Replace(0 + (InStr("," & Replace(Commastr, " ", ",") & ",", "," & Refstr & ",") > 0), -1, "X")
End Function
 
Upvote 0
Rick,

When I was writing the function I thought about how to make it shorter...some of your posts actually crossed my mind. You never fail to amaze me. Your code is as succinct as it comes. :)

Not the first time I've thought: What Would Rick Do?
 
Upvote 0
mrmmickle1 said:
Rick,

When I was writing the function I thought about how to make it shorter...some of your posts actually crossed my mind. You never fail to amaze me. Your code is as succinct as it comes. :)

Not the first time I've thought: What Would Rick Do?
Click to expand...
Thank you for your kind words... they are much appreciated. I have been programming in BASIC (a predecessor to VB), the compiled version of VB (a predecessor to VBA) and now VBA (along with a couple of other programming languages along the way) for a total of 35 years now... after all that time you develop an intimate familiarity with the functions/statements of a language as well as how they can be combined in order to produce a solution. I'll bet some people think I spend hours deriving some of compact code I post in this forum... well, I don't... I have been at this so long now that I believe I actually "think" in VB code when I read a problem and the overall structure to the solutions I post actually come to me almost immediately (then I have to "flesh them out" with the actual syntax afterwards). I believe the compacting of code part of my approach comes from my first computer (a TI99/4) which had a whopping total of 12,450 (plus or minus) bytes, yes, BYTES for the programmer to work with and, because BASIC was a interpreted language, part of that 12,450 bytes held the programming source code so the running program had even less memory in which to operate. You had to learn how to compact code in those days if you wanted to be able to create any kind of substantial programs (for the time). I got pretty good at compacting code back then and the thought processes behind doing so has apparently stayed with me until the present day. That is a long way of saying I cannot help myself but to create compact code... it is not a conscious effort... I have no choice any more as that is the way the programming part of my mind developed and, now, automatically works. I am glad you like some of the stuff I come up with though.
 
Upvote 0
Thanks for the background. That makes a lot of sense. The woman who first introduced me to programming has mentioned the necessity to create short programming code and to conserve every byte (i.e. database field lengths, declarations) in passing on more than one occasion. I would refer to the school of thought you and her are familiar with to an "Art of Programming" rather than just programming. I certainly appreciate this frame of mind, as it brings back fond memories of just getting started.

Why would you use more memory or space if it's not needed? :)
 
Upvote 0
Rick Rothstein said:
You can also write that UDF as a one-liner...
Code: 
Function StringOutX(Commastr As String, Refstr As Variant)
  StringOutX = Replace(0 + (InStr("," & Replace(Commastr, " ", ",") & ",", "," & Refstr & ",") > 0), -1, "X")
End Function
Click to expand...

Thank you mrmmickle1 and Rick. I've never used VBA for excel so I followed instructions on a tutorial website about to how to insert and run a VBA code. I saved the code but now the macro is not showing on the list. So I typed "StringOutX" and pressed "Run" button but then I got a message "Ambiguous name detected: StringoutX". Any idea what I did wrong?

Thank you for your help.
 
Upvote 0
yamamoto said:
Thank you mrmmickle1 and Rick. I've never used VBA for excel so I followed instructions on a tutorial website about to how to insert and run a VBA code. I saved the code but now the macro is not showing on the list. So I typed "StringOutX" and pressed "Run" button but then I got a message "Ambiguous name detected: StringoutX". Any idea what I did wrong?
Click to expand...
UDFs (user defined functions) are not macro... they are functions like SUM, LEN, MID, SIN, and so on... you don't run them, you use them in formulas just like any other Excel function. Look at the layout mrmmickle1 assumed for your data in Message #2... see the formula he showed to use in cell D2... you put that formula in that cell and then copy it across the row and then copy all of those cells down to the end of your data. If you really want a macro, we can construct one for you, just let us know.
 
Upvote 0
Rick Rothstein said:
UDFs (user defined functions) are not macro... they are functions like SUM, LEN, MID, SIN, and so on... you don't run them, you use them in formulas just like any other Excel function. Look at the layout mrmmickle1 assumed for your data in Message #2... see the formula he showed to use in cell D2... you put that formula in that cell and then copy it across the row and then copy all of those cells down to the end of your data. If you really want a macro, we can construct one for you, just let us know.
Click to expand...

Rick,
Thank you very much for your help. It worked wonderfully. Only issue with this is that there are many more names than the example... So I need to use a lookup table instead of listing dates in the first column next to names.

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









[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]ID
[/TD]
[/TR]
[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]









Desired format:

Excel 2010
ABCDEFGHI
JKLMNO
Aaron
Tom
Amanda
John

<colgroup><col style="background-color: #DAE7F5"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]ID
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3
[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0
[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]X
[/TD]

</tbody>
 
Upvote 0
Try setting up your data like this:


Excel 2010
AB
1IDDates
211, 5, 9, 13, 17, 21, 25, 29
322, 6, 10, 14, 18, 22, 26, 30
433, 7, 11, 15, 19, 23, 27
544, 8, 12, 16, 20, 24, 28
LookupSheet


Desired Result:


Excel 2010
ABCDEFGHIJKLMNO
1ID123456789101112
21AaronX000X000X000
32Tom0X000X000X00
43Amanda00X000X000X0
54John000X000X000X
Desired
Cell Formulas
RangeFormula
D2=(StringOutX(VLOOKUP($B2,LookupSheet!$A$2:$B$5,2,FALSE),D$1))



The UDF can be used in conjuction with other formulas...as shown in the above example.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
305
John_w
J
S
  • Solved
Few problems with dynamic arrays
2
Replies
15
Views
294
djclements
djclements
TryingToLearn
  • Question Question
Open a csv file but don't import all data and don't insert ""
Replies
1
Views
107
DanteAmor
DanteAmor
S
  • Solved
Sort within rows in same column
Replies
2
Views
143
sharshra
S
R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
83
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

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