Comparing lists in 2 workbooks and copying missing rows into new W

[Brian]

Hi,

I have two workbooks that I need to compare and then copy the data that is
missing from the original workbook (but present in the second one) into a
third new workbook. To complicate matters for every comparison the filename
of the workbooks to be compared chages.

I only need to compare rows as any changes will be the insertion of a new
row somewhere within the file.

Any help with a VBA code / macro that could do this would be extreamly
helpful.

Thank you very much

 

[Bob Bridges]

Well, let's work through it a piece at a time:

1) Get the user to name one or both files; there are various
get-the-file-name dialogues out there, but GetOpenFilename is already built
into Excel. (Or so it says here; I've never tired it, but I expect it's
easy.)

2) Open both workbooks, of course, along with some error checking to be sure
they're both there and both of the expected type.

3) You didn't say exactly what sort of comparison will be going on here, but
the usual is a list in both workbooks of some item, each item uniquely
identified by (say) student ID, part number, VIN, SSN, whatever. My own
favorite way of identifying which items on one list are (or are not) to be
found on the other uses the MATCH function in a helper column:

=MATCH(RC1,'Other sheet'!C5,0)

This gets me a list of row numbers for the items that are on the other
sheet, and #N/A for each item that isn't. Usually I want to check both ways,
but that's up to you; the point heer is that you'll put something like that
MATCH formula up and down some column in one or both sheets, and after that
your program can look at the results to cross-check all the items.

4) After that you can...well, you can do whatever you wanted your program to
do. But maybe it's time to stop and let you ask more detailed questions, or
maybe tell me I misunderstood your goal completely.

 

[Brian]

Thanks a lot for the quick reply Bob.

It sounds like a was a bit vague in my original question. Your solution was
helpful, but I am hoping to figure something out that is a bit more
automated. I am trying to build a macro that can be used by anyone in my
group so it needs to be a standalone spreadsheet.

To start I've put in sFileName = Application.GetOpenFilename so they can
then choose the 2 files ,which is working great!

The files being compared are about fifty columns across, but each has a
unique identifier in column C. Throughout the day we receive multiple of
these files and need a quick way to find which rows are new.

I was able to write a macro (which is below) that essentially compared row 1
in sheet 1 to row 1 in sheet 2 all the way down. This worked until there was
one new row of data and then everything after that was considered
"new/different" and was pulled into the output sheet.

Is there anyway to write a formula that will recognize that a row exists in
both sheets, even if the row letter numbers do not match, and therefore only
pull out the rows that do not have a match in any other row?

Dim wbkComp As Workbook
Dim wbkWith As Workbook
Dim wbkDiff As Workbook
Dim shtComp As Worksheet
Dim shtWith As Worksheet
Dim shtDiff As Worksheet
Dim lngCompRow As Long
Dim lngDiffRow As Long
Dim blnSame As Boolean
Dim intCol As Integer

Set wbkComp = Workbooks("Trade Blotter 2.xls")
Set wbkWith = Workbooks("Trade Blotter 1.xls")
Set wbkDiff = Workbooks.Add

For Each shtComp In wbkComp.Worksheets
Application.StatusBar = "Checking " & shtComp.Name
Set shtWith = wbkWith.Worksheets(shtComp.Name)
Set shtDiff = wbkDiff.Worksheets.Add
shtDiff.Name = "Diff " & shtComp.Name
lngCompRow = 1
lngDiffRow = 1
Do While shtComp.Cells(lngCompRow, 2) <> ""
blnSame = True
For intCol = 1 To 10
If shtComp.Cells(lngCompRow, intCol) <>
shtWith.Cells(lngCompRow, intCol) Then
blnSame = False
Exit For
End If
Next
If Not blnSame Then
shtComp.Rows(lngCompRow).Copy shtDiff.Cells(lngDiffRow, 1)
lngDiffRow = lngDiffRow + 1
End If
lngCompRow = lngCompRow + 1
Loop
Next
Application.StatusBar = False



Thanks a lot!

Brian

 

[Per Jessen]

Hi Brian

You say that column C has a unique identifier, does that mean that a
new entry is found if the identifier isn't found in both sheets, or do
we need to compare several columns once we have found a identifier
match ?

Can the identifier be found more than once in column C ?

Regards,
Per

 

[Brian]

Per,

Yes, a new entry is found if the identifier is found in one sheet but not
the other. After a new identifier is found I would like to take the entire
row and copy it to a third workbook.

Identifiers are unique and will only ever be used once.

Thanks alot,

Brian

 

[Per Jessen]

Hi Brian

This should do it (not tested):

Set wbkComp = Workbooks("Trade Blotter 2.xls")
Set wbkWith = Workbooks("Trade Blotter 1.xls")
Set wbkDiff = Workbooks.Add


For Each shtComp In wbkComp.Worksheets
Application.StatusBar = "Checking " & shtComp.Name
Set shtWith = wbkWith.Worksheets(shtComp.Name)
Set shtDiff = wbkDiff.Worksheets.Add
shtDiff.Name = "Diff " & shtComp.Name
LastRow = shtComp.Range("C1").End(xlDown).Row
lngDiffRow = 1
shtComp.Activate

For r = 1 To LastRow
Set Diff = shtWith.Columns("C").Find(what:=Cells(r,
"C").Value, LookAt:=xlWhole)
If Diff Is Nothing Then
Rows(r).Copy Destination:=shtDiff.Cells(lngDiffRow, 1)
lngDiffRow = lngDiffRow + 1
End If
Next
Next
Application.StatusBar = False
Application.ScreenUpdating = True

Regards,
Per

 

[Brian]

This is working perfectly. I can't thank you enough, Per!

Brian