Hello,
I'm using Excel 2007
I have two reports (last months report and this months report) that I need to compare. If the account number is the same I need to compare two columns on each worksheet and if it they have changed update the two cells on sheet1. If the account number is new I need to add the whole row to sheet1 from sheet2.
Let me know if I'm missing any needed information.
A macro would be preferred, but I'll take what ever advise I can get.
As always all any help is greatly appreciated,
David 92595
Here is a proposed simple macro that hard codes the data structure. You can change it to meet your needs.
Option Explicit
Sub CompareSheets()
'Compare two worksheets with VBA sheet names Sheet1 and Sheet2
'Data assumed to start in cell A1 and end in column 7 (G)
'Account numbers assumed to be in column 1
'Data are assumed to be sorted (increasing) with account number
'If data in Sheet2 columns D or F (4 or 6) are different in Sheet2,
' the these Sheet2 columns are copied to corresponding cells in Sheet1.
'If an account number in Sheet2 is not found in Sheet1, a new row is added
' to Sheet2 to hold all data from the corresponding row in Sheet1
Dim sheet1Row As Long
Dim sheet2Row As Long
sheet1Row = 2
sheet2Row = 2
'Loop over all rows until a blank row is found
Do
If Sheet1.Cells(sheet1Row, 1).Value = Sheet2.Cells(sheet2Row, 1).Value Then
'Same account number: copy different data in selected colums from Sheet2 to Sheet1
If Sheet1.Cells(sheet1Row, 4).Value <> Sheet2.Cells(sheet2Row, 4).Value Then
Sheet1.Cells(sheet1Row, 4).Value = Sheet2.Cells(sheet2Row, 4).Value
End If
If Sheet1.Cells(sheet1Row, 6).Value <> Sheet2.Cells(sheet2Row, 6).Value Then
Sheet1.Cells(sheet1Row, 6).Value = Sheet2.Cells(sheet2Row, 6).Value
End If
Else
'Different account number; copy all data to Sheet1
Sheet1.Rows(sheet1Row).Insert shift:=xlDown
Sheet2.Range(Sheet2.Cells(sheet2Row, 1), Sheet2.Cells(sheet2Row, 7)).Copy _
Destination:=Sheet1.Cells(sheet1Row, 1)
End If
sheet1Row = sheet1Row + 1
sheet2Row = sheet2Row + 1
Loop Until Cells(sheet2Row, 1).Value = ""
End Sub
I attached a zipped worksheet that contains the macro and three worksheets. Running the macro compares Sheet1 with Sheet2. The results for sheet1 should be the same as the sheet named RevisedSheet1
