Comparing two columns on different sheets (no case-sensitive/no space sensitive)

[nics87]

Hello everyone!

I rely on your expertise for this!

I need to compare two columns on different sheets and get all the non-duplicates on a third sheet. I would like the results on the third sheet to be red-highlitened and to show the the same row content present on the first sheet.

The comparison needs to be as flexible as possible, because capital letters and spaces might differ substantially, but the content is still the same.


I begin with this code, but it doesnt seem to work:

Sub Compare()
Dim count1 As Integer
count1 = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

Dim count2 As Integer
count2 = Sheet2.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

Dim index As Integer
index = 1

Application.ScreenUpdating = False
For i = 2 To count1
For j = 2 To count2
If Sheet1.Cells(i, 1).Value <> "" Or Sheet2.Cells(j, 2).Value <> "" Then
If StrComp(Sheet1.Cells(i, 1).Value, Sheet2.Cells(j, 1).Value, vbTextCompare) <> 0 Then

Sheet3.Cells(index, 1).Value = Sheet1.Cells(i, 1).Value
index = index + 1

Exit For

End If
End If
Next j
Next i

End Sub


Please help me out! Thank you!!

 

[DeBeuz]

Hi,

First of all count2 has Sheet1 in its offset, I think that is incorrect.
Also I think StrComp does not take into account the spacing.
Speed can also be increased since if there is nothing to compare, it is useless having J running.

So I tried to modify your code

Sub Compare()
    Dim count1 As Integer
    Dim count2 As Integer
    Dim index As Integer
    Dim Wrk1 As String
    Dim Wrk2 As String
    
    count1 = Sheets1.Range("A1").Offset(Sheets1.Rows.Count - 1, 0).End(xlUp).Row
    count2 = Sheets2.Range("A1").Offset(Sheets2.Rows.Count - 1, 0).End(xlUp).Row
    index = 1
    
    Application.ScreenUpdating = False
    For i = 2 To count1
        'No need to loop j if left value is empty
        If Sheets1.Cells(i, 1).Value <> "" Then
            'Make uppercase and remove spaces
            Wrk1 = UCase(Replace(Sheet1.Cells(i, 1).Value, " ", ""))
            For j = 2 To count2
                If Sheet2.Cells(j, 2).Value <> "" Then
                    'Make uppercase and remove spaces
                    Wrk2 = UCase(Replace(Sheet2.Cells(j, 1).Value, " ", ""))
                    If Wrk1 = Wrk2 Then
                        Sheet3.Cells(index, 1).Value = Sheet1.Cells(i, 1).Value
                        index = index + 1
                        Exit For
                    End If
                End If
            Next j
        End If
    Next i
    
End Sub

Does this do the job?

 

[nics87]

Thanks a lot for your help!! Unfortunately when I run it it says: "runtime error 424 - object required"

 

[nics87]

I also would need to add to your code something that allows the macro to also be "key words- sensitive". Any cells containing the same keywords should match!

 

[DeBeuz]

What line was higlighted during the 424 error?
Can you provide some examples of what you mean with "keyword-sensitive", since I do not see it in your first request.

 

[nics87]

The 424 was corrected (it displayed because we wrote "sheets1 and sheets2" instead of "sheet1 and sheet2).

Keywords-sensitive: I need a macro that is able to compare two lists which can sometimes contain strings which are not exactly the same but should be regarded as such....
For example:

sheet1 sheet2
A1: @Risk A12: @Risk Tool 1.1 Active


When comparing this two cells the current macro considers them different. Instead, the should be regarded as exactly the same thing. The word "@Risk" is the keyword that should make these cells match.

I hope this was clear enough

Thanks a lot, you are being very helpful!