How can I compare data in 2 seperate workbooks

[shreked12]

Hi ive looked all over the internet and totally confused now.

What it is I have 2 workbooks open and they have sheets within it.

What im trying to do is look at the sheet I have selected within workbook 1 and then automatically check that against the sheet I have selected in workbook 2 and if there is no data in workbook 2 but there is in workbook 1 I need the row highlighted.

Workbooks 1 & 2 do not have data all layed out in the same order BUT does have column headers of which is NLC/MACHINE/DATE/AMOUNT.

When looking down workbook 2 to see if there is a match with workbook 1 DATA MUST BE AN EXACT MATCH across all columns and they may not be in the same row number so I need it to look all the way down workbook 2 to see if there a match.

I was told that I can compare data within 1 workbook but im trying to compare against 2 workbooks and not copy and paste anything because of size of files.

I know there are online comparers but need to have it saved to a workbook so donno if it can be done as a macro or something else??

Any ideas?

 

[JLGWhiz]

We would need more detailed data about your workbook names, sheet names, header rows and number of columns to offer any meaningful code. But comparing two workbooks is no different than comparing two sheets, other than the qualification of the parent objects. ie. In the same workbook you look at Sheet1.Range("A:A") compared to Sheet2.Range("A:A"), whereas in different workbooks you look at Workbooks(1).Sheet1.Range("A:A") compared to Workbooks(2).Sheet1.Range("A:A") as and example.

 

[shreked12]

Right oh.

Donno if this helps but the file names that I want to use will not be the same file name all the time and thus if poss can I do as a personal macro so I can use in different ones.

In workbook 1 the data will always be on sheet 1 and the header columns as follows A=NLC B=MACHINE C=DATE D=AMOUNT and these will be along row 1 in both sheets.

There then will be data all down the workbook in rows with data in each column.

Workbook 2 the data will always be on a tab called "New Rec" and again will have A=NLC B=MACHINE C=DATE D=AMOUNT in row 1

does that help?

 

[JLGWhiz]

Assuming the code will run from Personal workbook.

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh1a As Worksheet
Dim shary, sh2 As Worksheet, fn As Range, col As Variant, s As Long, colA As Long, colB As Long, colC As Long, colD As Long
Appliccation.Calculation = xlCalculationManual
Set wb1 = Workbooks(1) 'substitute actual name for index #. eg. "MyWkbk.xlsx"
Set wb2 = Workbooks(2) 'always open this workbook after the one called workbooks(1)
Set sh1 = Workbooks(1).Sheets("Sheet1")
Set sh1a = Workbooks(1).Sheets("Sheet2")
Set sh2 = Workbooks(2).Sheets("New Rec")
shary = Array(sh1, sh1a)
    With sh2
        colA = .Rows(1).Find("NCLB", , xlValues).Column
        colB = .Rows(1).Find("MACHINE", , xlValues).Column
        colC = .Rows(1).Find("Date", , xlValues).Column
        colD = .Rows(1).Find("AMOUNT", , xlValues).Column
    End With
col = Array(colA, colB, colC, colD)
For s = 1 To 2
    With shary(s)
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            Set fn = sh2.Columns(colA).Find(c.Value, , xlValues)
                If Not fn Is Nothing Then
                    For i = LBound(col) To UBound(col)
                        If sh1.Cells(c.Row, i + 1) <> sh2.Cells(fn.Row, col(i)) Then
                            c.EntireRow.Interior.Color = vbYellow
                            Exit For
                        End If
                    Next
                Else
                    c.EntireRow.Interior.Color = vbYellow
                End If
        Next
    End With
Next
Application.Calculation = xlCalculationAutomatic
End Sub

I did not test this since you will test it anyhow. If I read your post correctly, you have two sheets in workbook 1 that you want to compare to a single sheet in another workbook, and that is how I wrote the code. Because of the size of your data and that you have to test all four columns for a match, it will take some time to run this code, but it should only be a couple of minutes at most.

 

[shreked12]

Excuse me for being thick but I copy and paste and save in a personal macro workbook?

I tried it and keep getting run time error.I thought it was just a spelling mistake but it still not work..

To actually to get it to run I open the 2 workbooks I need to compare and then run the personal macro??


Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh1a As Worksheet
Dim shary, sh2 As Worksheet, fn As Range, col As Variant, s As Long, colA As Long, colB As Long, colC As Long, colD As Long
Appliccation.Calculation = xlCalculationManual
Set wb1 = Workbooks(1) 'substitute actual name for index #. eg. "MyWkbk.xlsx"
Set wb2 = Workbooks(2) 'always open this workbook after the one called workbooks(1)
Set sh1 = Workbooks(1).Sheets("Sheet1")
Set sh1a = Workbooks(1).Sheets("Sheet2")
Set sh2 = Workbooks(2).Sheets("New Rec")
shary = Array(sh1, sh1a)
With sh2
colA = .Rows(1).Find("NCLB", , xlValues).Column
colB = .Rows(1).Find("MACHINE", , xlValues).Column
colC = .Rows(1).Find("Date", , xlValues).Column
colD = .Rows(1).Find("AMOUNT", , xlValues).Column
End With
col = Array(colA, colB, colC, colD)
For s = 1 To 2
With shary(s)
For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
Set fn = sh2.Columns(colA).Find(c.Value, , xlValues)
If Not fn Is Nothing Then
For i = LBound(col) To UBound(col)
If sh1.Cells(c.Row, i + 1) <> sh2.Cells(fn.Row, col(i)) Then
c.EntireRow.Interior.Color = vbYellow
Exit For
End If
Next
Else
c.EntireRow.Interior.Color = vbYellow
End If
Next
End With
Next
Application.Calculation = xlCalculationAutomatic
End Sub

 

[JLGWhiz]

Application was misspelled and there was some problem with the highlighting but the code below ran OK in a test set up. Give it a try.

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh1a As Worksheet
Dim shary, sh2 As Worksheet, fn As Range, col As Variant, s As Long, colA As Long, colB As Long, colC As Long, colD As Long
Application.Calculation = xlCalculationManual
Set wb1 = Workbooks(1) 'substitute actual name for index #. eg. "MyWkbk.xlsx"
Set wb2 = Workbooks(2) 'always open this workbook after the one called workbooks(1)
Set sh1 = wb1.Sheets("Sheet1")
Set sh1a = wb1.Sheets("Sheet2")
Set sh2 = wb2.Sheets("New Rec")
shary = Array(sh1, sh1a)
    With sh2
        colA = .Rows(1).Find("NCLB", , xlValues).Column
        colB = .Rows(1).Find("MACHINE", , xlValues).Column
        colC = .Rows(1).Find("Date", , xlValues).Column
        colD = .Rows(1).Find("AMOUNT", , xlValues).Column
    End With
col = Array(colA, colB, colC, colD)
For s = LBound(shary) To UBound(shary)
    With shary(s)
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            Set fn = sh2.Columns(colA).Find(c.Value, , xlValues)
                If Not fn Is Nothing Then
                    For i = LBound(col) To UBound(col)
                        If sh1.Cells(c.Row, i + 1) <> sh2.Cells(fn.Row, col(i)) Then
                            Rows(c.Row).Interior.Color = vbYellow
                            Exit For
                        End If
                    Next
                Else
                    c.EntireRow.Interior.Color = vbYellow
                End If
        Next
    End With
Next
Application.Calculation = xlCalculationAutomatic
End Sub

You can put this code in workbook 1 if you do not want to run it from the personal workbook. jJust make sure it is a macro enabled workbook, of course.

 

[shreked12]

Thanks for that but I keep getting run time error when I get to Set sh2 = wb2.Sheets("New Rec").


I am right in what im doing? Ive saved the macro into a personal workbook. Im opening the workbook that has data in sheet 1 in first and then opening workbook that has sheet called new tab in it.

Im then running the macro and then keep getting run time error. I have the tab called "new Rec" in it. What am I doing wrong and please see picture of tabs in workbook that has the named tab in it.

https://ibb.co/3rWSGnG

 

[JLGWhiz]

See the comments in the code body concerning file names and sheet names. Make sure the file and sheet names in the code are the same as those in the actual files. Also, when you get errors be sure to include what the error is when you post back for assistance. We cannot see your files as you run the code, so you need to relate that information in the thread.

 

[shreked12]

Ladies and Gents

Im completely confusing everyone and this is what I need and dont know if we can amend macro shown at bottom

I have 2 seperate workbooks that I have data in that I need to compare.

Problem is that at all times both workbooks will have different names but the 1st workbook will ALWAYS start with the name CUBIC/SHERE/FASTIS and then some numbers.

The 2nd workbook will always start with the name SDCI SUMMARY and again will have some numbers after.
Within the workbook called SDCI summary there are tabs called CUBIC/SHERE/FASTIS

What im trying to do is compare the data in workbook 1 against the tab with the same name as in cubic etc in the file called SDCI summary.
To make life easier I will open manually the 2 files that I want to compare and select the tab in SDCI summary file.
All columns on both workbooks have headers of which are NLC/MACHINE/DATE /AMOUNT

What im trying to do is look in workbook 1 and then look in tab in SDCI summary file(workbook 2 ) to see if there is a exact match. I need it to look all the way down the tab in the workbook called SDCI summary as data will not be in the same line in both workbooks.
If there is a match then that fine no action required, If there is NO match in SDCI summary (workbook 2) I need the row that dont match highlighted in the tab im comparing in SDCI SUMMARY.



Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh1a As Worksheet
Dim shary, sh2 As Worksheet, fn As Range, col As Variant, s As Long, colA As Long, colB As Long, colC As Long, colD As Long
Application.Calculation = xlCalculationManual
Set wb1 = Workbooks(1) 'substitute actual name for index #. eg. "MyWkbk.xlsx"
Set wb2 = Workbooks(2) 'always open this workbook after the one called workbooks(1)
Set sh1 = wb1.Sheets("Sheet1")
Set sh1a = wb1.Sheets("Sheet2")
Set sh2 = wb2.Sheets("New Rec")
shary = Array(sh1, sh1a)
With sh2
colA = .Rows(1).Find("NCLB", , xlValues).Column
colB = .Rows(1).Find("MACHINE", , xlValues).Column
colC = .Rows(1).Find("Date", , xlValues).Column
colD = .Rows(1).Find("AMOUNT", , xlValues).Column
End With
col = Array(colA, colB, colC, colD)
For s = LBound(shary) To UBound(shary)
With shary(s)
For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
Set fn = sh2.Columns(colA).Find(c.Value, , xlValues)
If Not fn Is Nothing Then
For i = LBound(col) To UBound(col)
If sh1.Cells(c.Row, i + 1) <> sh2.Cells(fn.Row, col(i)) Then
Rows(c.Row).Interior.Color = vbYellow
Exit For
End If
Next
Else
c.EntireRow.Interior.Color = vbYellow
End If
Next
End With
Next
Application.Calculation = xlCalculationAutomatic
End Sub

 

[JLGWhiz]

1st workbook will ALWAYS start with the name CUBIC/SHERE/FASTIS

Excel will not allow "/" symbols in file names. You need to be accurate with the file names to get good code written. What you have shown here could be part of the path to the file name, but you need to be accurate with either the full path, or just the file name part of the full name. I understand that there is a suffix of numbers that are part of the name.