GetMatch plus VBA Code to Extract Matched Numbers from 2 Single Cells

Hi, This David

Please i need some help on GetMatch Formula and the VBA Macro..

Here is the Problem:

I have First Group of Number is In Cell C6 -> 28-17-20-32-21-18-25-40-50-60
the Second Group Of Numbers is In Cell C9 -> 18-19-20-21-22-23-24-40-50-60

i Need to Extract The Numbers that Match in the 2 Groups (20 21 18 40 50 60) and copy In Cell F6


Please i need a Correction

I Inserted the Following VBA Code :

Function GetMatch(LookFor, LookIn)
    GetMatch = ""
    StartPos = 1
    For i = 1 To Len(LookFor)
        If Mid(LookFor, i, 1) = "-" Then
            ThisStr = Mid(LookFor, StartPos, (i - StartPos))
            If InStr(LookIn, ThisStr) > 0 Then
                GetMatch = GetMatch & ThisStr & ", "
            End If
            StartPos = i + 1
        End If
    Next i
    ThisStr = Mid(LookFor, StartPos, (i - StartPos))
    If InStr(LookIn, ThisStr) > 0 Then
       GetMatch = GetMatch & ThisStr & ", "
    End If

    If Len(GetMatch) > 3 Then
        GetMatch = Left(GetMatch, Len(GetMatch) - 2)
    End If

End Function

And in Cell F6 (where i need the result) I Copy the following Formula :

 =GetMatch(C6,C9)

Then when I Run the sheet i got i Error message :

Please i need some expert to revise my mistake and tell me what is wrong....



Thanks !!!

david gonzalez,


The following Function works correctly in your worksheet.

Instructions within the Function:

' If C6 = 28-17-20-32-21-18-21-40-50-60
' If C9 = 18-19-20-21-22-23-24-40-50-60
' The Function/Formula in F6
' =GetMatchV2(C6,C9)
' F6 will display: 20-21-18-21-40-50-60


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Function GetMatchV2(LookFor As String, LookIn As String) As String
' stanleydgromjr, 02/17/2013
' http://www.excelforum.com/excel-programming-vba-macros/900473-getmatch-plus-vba-code-to-extract-matched-numbers-from-2-single-cells.html
' If C6 = 28-17-20-32-21-18-21-40-50-60
' If C9 = 18-19-20-21-22-23-24-40-50-60
' The Function in F6
'   =GetMatchV2(C6,C9)
' F6 will display: 20-21-18-21-40-50-60
Dim s, i As Long, h As String
s = Split(LookFor, "-")
For i = LBound(s) To UBound(s)
  If InStr(LookIn, s(i)) > 0 Then
    h = h & s(i) & "-"
  End If
Next i
If Right(h, 1) = "-" Then h = Left(h, Len(h) - 1)
GetMatchV2 = h
End Function

Follow the instructions to input the Function into F6

david gonzalez,

If you require Function GetMatchV2 to return a Unique result with no duplicates, I will adjust the Function for you.

JBeaucaire,

Nice catch.

Let's see what david gonzalez says.

david gonzalez,

The following Function addresses JBeaucaire's concerns.

Option Explicit
Function GetMatchV3(LookFor As String, LookIn As String) As String
' stanleydgromjr, 02/17/2013
' http://www.excelforum.com/excel-programming-vba-macros/900473-getmatch-plus-vba-code-to-extract-matched-numbers-from-2-single-cells.html
' If C6 = 28-17-20-32-21-18-21-40-50-60
' If C9 = 18-19-20-21-22-23-24-40-50-60
' The Function in F6
'   =GetMatchV3(C6,C9)
' F6 will display: 20-21-18-21-40-50-60
Dim s1, s2, i As Long, ii As Long, h As String
s1 = Split(LookFor, "-")
s2 = Split(LookIn, "-")
For i = LBound(s1) To UBound(s1)
  For ii = LBound(s2) To UBound(s2)
    If s1(i) = s2(ii) Then
      h = h & s1(i) & "-"
    End If
  Next ii
Next i
If Right(h, 1) = "-" Then h = Left(h, Len(h) - 1)
GetMatchV3 = h
End Function

As long as the strings in the cells are all the same number of digits these approaches will work, but if the search strings were two digits and the searched string had longer digits, you would get false positives without adjusting these methods to splitting both strings and iterating through both arrays completely. Just a warning.

For instance a search for "20" from this string would give a false positive:


28-17-202-32-21-18-21-40-50-60

Hi stanleydgromjr and JBeaucaire

Thank you for your quick response !!

Yes, Mr JBeaucaire is right,

I Post as example and to be short the Match of Cell C6+C9 (with few numbers on each cell)
But in reality there are going to be more than 30 rows combined against other 6 rows to find the match.
and each of the Cell might will have between 12 to 22 Numbers, the Max Number will vary between 36 to 60 (but mostly 36).. (outch...)

Mr stanleydgromjr, i'm noob in excel but i'm trying real hard to learn, and i'm asking my self (please no hard feeling)

Inside the code is the example that i've posted.

'If C6 = 28-17-20-32-21-18-21-40-50-60"
'If C9 = 18-19-20-21-22-23-24-40-50-60"

The question is : the code always will look for those result numbers?


Thank you, and i'm sorry to bother with might (non sense) question (Smile)

Here are threads where I've dealt with this in the past, too.

http://www.excelforum.com/excel-prog...ml#post3045081
http://www.excelforum.com/showthread...=1#post2950219

david gonzalez,


Have you tried my Function GetMatchV3? It does work in your attached workbook.

Add my Function GetMatchV3 to a Module.

In your workbook cell F6, try:
=GetMatchV3(C6,C9)

david gonzalez,

Thanks for the feedback.

You are very welcome. Glad we could help.

Come back anytime.

david gonzalez

I tried jindon's function.

Please try the slightly modified jindon's code below - it will return a unique sorted result.

Option Explicit
Function GetMatch(LookFor As String, LookIn As String) As String
' jindon, 02/18/2013
' jindon's code has been modified by stanleydgromjr, 02/18/2013
' http://www.excelforum.com/excel-programming-vba-macros/900473-getmatch-plus-vba-code-to-extract-matched-numbers-from-2-single-cells.html
Static SL As Object
Dim e, i As Long
If SL Is Nothing Then
  Set SL = CreateObject("System.Collections.SortedList")
End If
SL.Clear
For Each e In Split(LookFor, "-")
  SL.Item(e) = Empty
Next
For Each e In Split(LookIn, "-")
  If SL.Contains(e) Then SL(e) = e
Next
For i = 0 To SL.Count - 1
  If Not IsEmpty(SL.GetByIndex(i)) Then
     GetMatch = Trim$(GetMatch & " " & SL.GetKey(i))
  End If
Next
End Function

In cell F6 type in:
=GetMatch(C6,C9)

Try this

Function GetMatch(LookFor As String, LookIn As String) As String
    Static SL As Object
    If SL Is Nothing Then
         Set SL = CreateObject("System.Collections.SortedList")
    End If
    SL.Clear
    For Each e In Split(LookFor, "-")
        SL.Item(e) = Empty
    Next
    For Each e In Split(LookIn, "-")
        If SL.Contains(e) Then SL(e) = e
    Next
    For i = 0 To SL.Count - 1
        If Not IsEmpty(SL.GetByIndex(i)) Then
            GetMatch = Trim$(GetMatch & " " & SL.GetKey(i))
        End If
    Next
End Function