Reconciliation of data from 3 different worksheets

[K Jenkins]

Hi,

I am new here and could not find if it was posted again but I want to compare data from 3 different Excel files so as to find whether the 3 different sheets contain the same data or if some are missing.

I am using the lookup function for now but I would like something more sophisticated or something from VBA maybe.

I would appreciate any help

 

[Yongle]

Hi, welcome to the forum

Q1 What EXACTLY are you wanting to check?

Q2 What do you want to know if differences are found?

example
- Customer Names appear in columnA in sheet1 in all 3 workbooks
- I want to make sure that EVERY Customer Name is contained in EVERY workbook
- I want to know which Customer Names are missing from EACH workbook

Q3 How many rows (approx) does each workbook contain? (less than 1,000? less than 10,000? etc)

Q4 Does EACH workbook contain ONLY 1 sheet?

Q5 Is the data that is being checked in the SAME column in EACH workbook?

 

[K Jenkins]

Hi and thank you for welcoming me and for the reply

A1 I want to check travel records.
A2 I want to find the differences that the 3 provided sheets have between them.
Ex.
Travel record1 is in both first 2 sheets but not in the 3rd.
Travel record2 appears only in the 1st sheet.
A3 About 1000.
A4 Yes, 1.
A5 The datasheets have different headings and columns but the data are presented basically in the same way. But to answer you, no there are not.

I hope I gave you a clearer view

Feel free to ask for more clarifications.

 

[Yongle]

In the code below:
- the values from wb1 (column A), wb2 (column D) and wb3 (column E) are placed in 3 arrays (a1,a2,a3)
- the values from the arrays are placed in a single column de-duped and sorted
- in a separate column (one for each workbook) each record is marked with a "No" if not found in a particular workbook

The report looks like this

RECORDS	wb1.xlsx	wb2.xlsx	wb3.xlsx
TR001		No	No
TR002			No
TR003		No
TR004			No
TR005		No	No
TR006
TR007		No	No
TR008			No
TR009		No
TR010			No
TR011		No	No
TR012	No
TR013		No	No

<tbody>
</tbody>

Testing the code
- insert code below in a NEW workbook
- amend each path and file name
- amend column letter to column containing common record identifier (c1 pertains to wb1 etc)
- you did not tell me which columns contain the record ID in each workbook
- if column "A" in each of you workbooks contains the record ID then amend to c1 ="A", c2="A", c3 ="A"
- run CreateReport

Option Explicit
    Const wb1 = "[COLOR=#ff0000]C:\FullPath\ToFile1\wb1.xlsx[/COLOR]"
    Const c1 = "[COLOR=#ff0000]A[/COLOR]"
    Const wb2 = "[COLOR=#000080]C:\FullPath\ToFile2\wb2.xlsx[/COLOR]"
    Const c2 = "[COLOR=#000080]D[/COLOR]"
    Const wb3 = "[COLOR=#008080]C:\FullPath\ToFile3\wb3.xlsx[/COLOR]"
    Const c3 = "[COLOR=#008080]E[/COLOR]"
    Dim a1, a2, a3, a
    Dim wb As Workbook, ws As Worksheet, cel As Range, rng As Range

Sub CreateReport()
    Application.ScreenUpdating = False
    CreateArrays
    ListAllRecords
    ClearNoFlag
End Sub

Private Sub CreateArrays()
    Set wb = Workbooks.Open(wb1)
        With wb.Sheets(1)
            a1 = .Range(.Cells(2, c1), .Cells(.Rows.Count, c1).End(xlUp))
        End With
    wb.Close False
    Set wb = Workbooks.Open(wb2)
        With wb.Sheets(1)
            a2 = .Range(.Cells(2, c2), .Cells(.Rows.Count, c2).End(xlUp))
        End With
    wb.Close False
    Set wb = Workbooks.Open(wb3)
        With wb.Sheets(1)
            a3 = .Range(.Cells(2, c3), .Cells(.Rows.Count, c3).End(xlUp))
        End With
    wb.Close
End Sub

Private Sub ListAllRecords()
    Set wb = ThisWorkbook
    Set ws = wb.Sheets.Add(before:=wb.Sheets(1))
        With ws
        'add headers
            .Cells(1, 1) = "RECORDS"
            .Cells(1, 2) = Right(wb1, Len(wb1) - InStrRev(wb1, "\"))
            .Cells(1, 3) = Right(wb2, Len(wb2) - InStrRev(wb2, "\"))
            .Cells(1, 4) = Right(wb3, Len(wb3) - InStrRev(wb3, "\"))
        'add records
            .Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(a1)) = a1
            .Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(a2)) = a2
            .Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(a3)) = a3
            Set rng = .Range(.Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        'remove duplicates
            rng.RemoveDuplicates Columns:=1, Header:=xlYes
            Set rng = .Range(.Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        'sort records
            rng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
        'add "No" flag
            rng.Offset(1, 1).Resize(rng.Rows.Count - 1, 3).Value = "No"
        End With
End Sub

Private Sub ClearNoFlag()
    For Each cel In rng
        For Each a In a1
            If a = cel Then
                cel.Offset(, 1).ClearContents
                Exit For
            End If
        Next a
        For Each a In a2
            If a = cel Then
                cel.Offset(, 2).ClearContents
                Exit For
            End If
        Next a
        For Each a In a3
            If a = cel Then
                cel.Offset(, 3).ClearContents
                Exit For
            End If
        Next a
    Next cel
End Sub

 

[K Jenkins]

Awesome

This seems GREAT!!!

You created the full code. Thank you very much