asked on

Excel Macro to Compare between Two Worksheets

Hello,

Please see attached for file structure. I'm looking for a macro that will do the following starting on File_A worksheet row 2:

- IF File_A column C matches File_B column U then Compare File_A column H against File_B column Q. If there is no match, enter YES in column O for that row.

- IF File_A column C matches File_B column U AND File_A column H matches File_B column Q then Compare File_A column G against File_B column O. If there is no match, enter YES in column N for that row.

- IF File_A column C matches File_B column U AND File_A column H matches File_B column Q AND File_A column G matches File_B column O then Compare File_A column K against File_B column R. If there is no match, enter YES in column P for that row.

Please provide macro solution only as this is part of a more complex process. Likely clear as mud but let me know if you have questions. I'm using Excel Office 365 64-bit.

Thanks!
EE-Sample.xlsx

Subodh Tiwari (Neeraj)

Please give this a try...

Place this sub routine on a Standard Module...

Sub CompareColumnsInTwoSheets(FileA As Worksheet, FileB As Worksheet)
Dim rng As Range, cel As Range
Dim fRng As Range
Dim lr As LongPtr

lr = FileA.Cells(Rows.Count, "C").End(xlUp).Row

Set rng = FileA.Range("C2:C" & lr)

For Each cel In rng
    Set fRng = FileB.Range("U:U").Find(what:=cel.Value, lookat:=xlWhole)
    If Not fRng Is Nothing Then
        If FileA.Cells(cel.Row, "H") = FileB.Cells(fRng.Row, "Q") Then
            FileA.Cells(cel.Row, "O").Value = "YES"
            
            If FileA.Cells(cel.Row, "G") <> FileB.Cells(rng.Row, "O") Then
                FileA.Cells(cel.Row, "N").Value = "YES"
            Else
                If FileA.Cells(cel.Row, "K") <> FileB.Cells(rng.Row, "R") Then FileA.Cells(cel.Row, "P") = "YES"
            End If
        End If
    End If
Next cel
End Sub

Open in new window

And then call it within your main macro whenever required like below...

Sub MainMacro()
Dim file_A As Worksheet, file_B As Worksheet
Set file_A = Worksheets("File_A")
Set file_B = Worksheets("File_B")

'Call CompareColumnsInTwoSheets like below whenever required

Call CompareColumnsInTwoSheets(file_A, file_B)

End Sub

Open in new window

Ron Bayes

ASKER

I cannot seem to get this to run. Errors out at the call statement. Can you add the code to the sample workbook I originally provided and attach so I can review?

Subodh Tiwari (Neeraj)

Okay, please find the attached with the codes on Module1.
Please run the code "MainMacro".

I have also tweaked the code as per the matching conditions and added comments in there.

I have inserted a button called "Compare Sheets" on File_A Sheet so that you can run the code by clicking that button also.
EE-Sample_CompareSheets.xlsm

Ron Bayes

ASKER

Thank you. The only thing that does not appear to be working is the Hours Mismatch column. For example, row 1 shows 8.0 hours in column K but the match on File_B shows 7.50. So there should be a Yes in that cell as the hours do not match.

Ron Bayes

ASKER

Adding to that....I believe all three rows in File_A would show a mismatch based on comparison between the two sheets. So for rows 3 and 4 both the Reason Mismatch and the Hours Mismatch should show YES.

Subodh Tiwari (Neeraj)

I think you should go through the conditions you posted in the description and I only followed those conditions.

It seems that if FileA col. C is found in FileB col. U, you are trying to track all the mismatches. Is that correct?

Anyways, please find the two files and see which one works for you.
EE-Sample_CompareSheets-_A.xlsm
EE-Sample_CompareSheets-_B.xlsm

Ron Bayes

ASKER

Perhaps I'm making this too complex. At this point all I want is to compare the ID and date fields. If the date field is wrong I want an indicator. If I strip your code down so its only looking at date comparison its showing incorrect result. Based on attached when macro runs, cells O3 and O5 should be blank as the unique ID (column C) matches and the Start Date (column H) matches the other file.

If we can get just this one part to work then I'll be set. Appreciate your assistance and patience.
EE-Sample_CompareSheets-_B_V2.xlsm

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ron Bayes

ASKER

Thank you - I believe this gets me closer.

Subodh Tiwari (Neeraj)

You're welcome!