Compare the columns in two excel workbooks

Hi and Welcome to the Forum

Your request to compare two columns of data is fairly usual ...

However, you could make your life a lot easier ... if the two columns to be compared where located in the same workbook.

Would it be feasible ... or not ?

Attached is a combined test file for your review

Hello Julia,

Would you agree to store your macro in the "main" workbook : Employee_Amounts ?

OK then ...

Below is the macro to be tested:

Sub Status()
' Make sure to Open file "Employees BS" BEFORE running the Macro '''''''''''''''''''''''''''''''''''
Dim i As Long, lastr As Long
' Determine Last Row in Active Sheet
lastr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
    For i = 2 To lastr
        Cells(i, 4) = Evaluate("=IF(AND(B" & i & "=C" & i & ",IFERROR(MATCH(A" & i & ",'[Employees BS.xlsx]Sheet1'!$A:$A,0),"""")=""""),""OK"",""Not OK"")")
        If Cells(i, 4) = "OK" Then
            Cells(i, 4).Interior.Color = RGB(146, 208, 80)
        Else
            Cells(i, 4).Interior.Color = RGB(244, 176, 132)
        End If
    Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Hope this will help

Hello Julia,

Thanks a lot for your Thanks AND for the Like

Hello,

Allow me to quote a Computer Engineer by the name of Jorge Castro:

Quote

SAP is not bad, IT IS HORRIBLE !!!

Let’s say that somebody sells you a system that takes a dozen of “Experts” to configure for almost 2 years (if you are lucky) and you get practically nothing but a program with an old interface that does NOTHING but you must build all middle-ware for to obtain something ....

and then download everything to Excel and work like crazy to design all sorts of patches ... !!!

Plenty of solutions ... !!!

The Top One is to Get rid of SAP ... !!!

Joking aside ... can you get the consolidated file you need right from your wonderful mainframe ...?

or

Do you have to build it yourself using the two separate downloads on your PC ...?

OK ...

So which workbook is going to be the ELECTED one to swallow the other one ...?

Great ... !!!

So now we do need to clarify your repetitive process ....

Say, you do download the other file : "Employees BS"

Obviously, this worksheet will have to be either copied or moved ...

It depends a lot on your actual working procedure. The very first question is :

Is each "Employees BS" file totally brand new ... or do you plan to add records in sequence each and every time ?

To recap and confirm ...

Your main file will hold two worksheets :

1. the Master sheet showing all your data and the Status in Column D to be checked

2. the Data sheet where each and every import will add records to Column A

Is that right ?

By the way, I keep wondering who's got the brilliant idea to add the two letters BS at the end of the Employees file ....???

probably SAP ... extremely proficient when it comes to BS ...!!!

Quote from Julia kumr

Hahahahahahaha I hope you mean BS- Beautiful Sheet

Regarding first two points,

Yes, that's right

Display More

SOOOOO BEAUTIFUL ... !!!