CODEWITHSUNDEEP
vbaexcel
Empty
Empty

Reputation: 123

Macro VBA - Comparision the similar numbers from both strings

I am new in Macro VBA and I am facing a problem.

I having two string to compare, and how do I get the string as Result shown if the similarity numbers found in both string?

string 1 : 1,2,3,4,6,7,8,9,10,11,12,13,19,20

string 2 : 2,3,7,8,9,10,11

After comparison:

Result : 2,3,7,8,9,10,11

Code:

If ActiveSheet.Cells(irow + 1, 12).Value = "" Then

    'MsgBox "Data not found"
Else
    temp = vbNullString
    temp = ActiveSheet.Cells(irow + 1, 12).Value
    'expanddata() use to expend a sequence of numbers into a display string as below
    ' 1,2-4,6 -> 1,2,3,4,6
    temp = expanddata(temp) 

    If Worksheets("AI").Cells(irow + 1, 10).Value = temp Then
        temp = ConvNum(temp) 'if whole string same then convert back to 1,2-4,6
    Else
        'the comparision make in here        
    End If
Worksheets("AI").Cells(irow + 1, 10) = temp

End If

Thank you.

Upvotes: 0

Views: 94

Answers (4)

brettdj
brettdj

Reputation: 55702

Automating powershell to print the list to a text file c:\temp\test.txt

Sub Test()
a = "(1,2,3,4,6,7,8,9,10,11,12,13,19,20)"
b = "(2,3,7,8,9,10,11)"
cmd = Shell("powershell.exe """ & a & """ | Where {""" & b & """ -Contains $_}  | out-file c:\temp\test.txt", 1)
End Sub

Upvotes: 1

user3598756
user3598756

Reputation: 29421

try this

Option Explicit

Function CompareStrings(string1 As String, string2 As String) As String
Dim s As Variant

For Each s In Split(string1, ",")
    If "," & string2 & "," Like "*," & s & ",*" Then CompareStrings = CompareStrings & s & ","
Next s

CompareStrings = Left(CompareStrings, Len(CompareStrings) - 1)
End Function

which could be called as follows

Sub main()

Dim string1 As String, string2 As String, stringRes As String

string1 = "1,2,3,4,6,7,8,9,10,11,12,13,19,20"
string2 = "2,3,7,8,9,10,11"

stringRes = CompareStrings(string1, string2)

MsgBox stringRes
End Sub

Upvotes: 0

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Please try the below code.

Sub comparestring()
    string1 = "1,2,3,4,6,7,8,9,10,11,12,13,19,20"
    string2 = "2,3,7,8,9,10,11"
    str1 = Split(string1, ",")
    str2 = Split(string2, ",")
    For i = 0 To UBound(str1)
        For j = 0 To UBound(str2)
            If str1(i) = str2(j) Then
                If matchedcontent <> "" Then
                    matchedcontent = matchedcontent & "," & str1(i)
                Else
                    matchedcontent = str1(i)
                End If
            End If
        Next j
    Next i
    Range("A3").Value = matchedcontent
End Sub

Assign the two strings to string 1 and string 2 like below results will be printed at Cells A3

string1=Activesheet.Range("A1").Value
string2=Activesheet.Range("A2").Value

Upvotes: 0

Empty
Empty

Reputation: 123

    For irow = 1 To numofrow
        ptcolno = 12
        If ActiveSheet.Cells(irow + 1, 12).Value = "" Then
            'MsgBox "Data not found"
        Else
            temp = vbNullString
            temp = ActiveSheet.Cells(irow + 1, 12).Value
            temp = expanddata(temp)

            If Worksheets("AI").Cells(irow + 1, 10).Value = temp Then
                temp = ConvNum(temp)
            Else
                ' Answer
                Temp2 = Worksheets("AI").Cells(irow + 1, 10).Value
                arr1 = Split(Temp2, ",")
                arr2 = Split(temp, ",")

                temp = vbNullString
                For i = LBound(arr2) To UBound(arr2)
                    For j = LBound(arr1) To UBound(arr1)
                        If arr2(i) = arr1(j) Then
                            temp = temp & "," & arr2(i)
                        End If
                    Next j
                Next i
                temp = Right(temp, Len(temp) - 1)
                temp = ConvNum(temp)
                ' End
            End If
            Worksheets(checktype & "_BUYOFF_1").Cells(irow + 1, 68) = temp

Upvotes: 0

Related Questions