Find numerical values in one cell,within another cell- is it possi

[Guest]

I am wondering if it is possible to create a macro to compare HLA data for
matches. HLA data is looking for the presence of values in column a in
column b. It is also looking for no numerical data that is not present in
column b to be present in column a. Here are a couple of examples:

column a column b
1,2 1,2,3 match since 1,2 in column a are
found in column b
1,2,7 1,2,3 mismatch since 7 in column a is not
found in columnb
1,2,3 1,2,3,7 match since 1,2,3 in column a are
found in column b

If anyone could provide help with a macro that would help with this data I
would really appreciate it.
Thank you,
E

 

[Tom Ogilvy]

for the simplest comparison:
set rng = Range(cells(1,1),cells(1,1).End(xldown))
for each cell in rng
if instr(cell.offset(0,1),cell) then
cell.offset(0,2).Value = "Match"
else
cell.offset(0,2).Value = "Mismatch"
End if
Next

I can envision many scenarios where this would fail or fail sometimes, but
you have not espoused these, so no use overcomplicating.

 

[JE McGimpsey]

Here's a UDF I made a while ago:

Public Function IsElementOf( _
LookFor As String, LookIn As String, _
Optional Delimiter As String = ",") As Boolean
Dim vElements As Variant
Dim vUniverse
Dim i As Long
Dim j As Long
Dim bTemp As Boolean

vElements = Split(LookFor, Delimiter)
vUniverse = Split(LookIn, Delimiter)
If UBound(vElements) <= UBound(vUniverse) Then
For i = LBound(vElements) To UBound(vElements)
bTemp = False
For j = LBound(vUniverse) To UBound(vUniverse)
If vElements(i) = vUniverse(j) Then
vUniverse(j) = Empty
bTemp = True
Exit For
End If
Next j
If bTemp = False Then Exit For
Next i
End If
IsElementOf = bTemp
End Function


Usage: =IsElementOf(A1,B1)

Note that this requires a 1:1 match between a and b, so

1,2,3,3 1,2,3

will generate FALSE. If you want that to return TRUE, delete or comment
out the

vUniverse(j) = Empty

line