I am trying to write some code that will allow a user to compare data
columns from two separate sheets based on one or more key columns.
The first sheet comes from some laboratory instruments which generate
data associated with one or more columns making a unique key. The
second sheet is a scrubbed version of the first sheet to send to a
client. It also will have the key columns but their position may
vary. The goal is to make sure the second sheet's data columns match
those of the first.

There is a user form that allows one to select which columns on each
sheet represent the keys and which represent the data.

I currently am using several nested FOR loops which traverse through
each sheet to find the matching keys then compare the data columns.
Differences or duplicates are printed into a separate sheet for
reference.
One caveat is that I am not allowed to make changes to either source
or destination sheet.

Here is my algorithm:

For loop through each destination row
For loop through each source row
For loop through each key column
Compare key column
If key columns don't match then Exit For(go to next source
row)
For loop through each data column (All keys match)
Compare data columns and print problems
Next
Next
Next
Next

It works, but is quite inefficient.

Would there be any suggestions on how I might use any Excel built-in
functions, Range methods or such in order to improve the process?

Thank You!

I'm not sure I understand, so I'll be very vague <vbg.

I can think of a couple of different ways (none of them may be the best,
though).

Since you can't touch the worksheets, copy the worksheet to be searched to
another workbook. It's a copy and do whatever you need to it!

1. Apply Data|filter|autofilter to that worksheet.
Filter each column by the values in the "source" worksheet that should
match.
If you find a row that's still visible, then do the comparison for the data
columns.

2. Use .find to find the first key. Then look at the other key columns to
see if they match. If yes, then stop looking. If now, then repeat the
.find and check the next row--until you've found a match or you're back to
your first key match.
(You wouldn't need to copy the worksheet for this one.)

3. Copy the sheet.
use a formula in column IV that concatenates the keys.
concatenate your source keys.
(I used &char(1)& in the formula and &chr(1)& in code. There's a good
chance that character was never used.)
Convert that formula range in column IV to values (makes things quicker???)
Search the IV range for the key value
if found, do a cell by cell comparison

If you could have one key with several matches, I think I'd use the autofilter
approach. If you only have a max of one match, I'd use the 3rd approach.


Without knowing anything about the columns to compare and the cells that make
the keys...

Option Explicit
Sub testme01()

application.screenupdating = false

Dim myCell As Range
Dim myRng As Range
Dim myCols As Variant
Dim iCtr As Long
Dim myLookinRng As Range

Dim sourceWks As Worksheet
Dim LookinWks As Worksheet
Dim rptWks As Worksheet

Dim res As Variant
Dim oRow As Long
Dim myKey As String

Set sourceWks = Worksheets("sheet1")

Worksheets("sheet2").Copy 'to a new workbook
Set LookinWks = ActiveSheet

Set rptWks = Workbooks.Add(1).Worksheets(1)
rptWks.Range("a1").Resize(1, 4).Value = Array("Key", "Col", "Source", "Dest")
oRow = 1

myCols = Array(8, 10, 12)

With sourceWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With LookinWks
With .Range("IV1:IV" & .Cells(.Rows.Count, "A").End(xlUp).Row)
.Formula = "=a1&char(1)&b1&char(1)&e1"
.Value = .Value
Set myLookinRng = .Cells
End With
End With

For Each myCell In myRng.Cells
With myCell
myKey = .Value & Chr(1) & .Offset(0, 1).Value & Chr(1) & _
.Offset(0, 4).Value
res = Application.Match(myKey, myLookinRng, 0)
If IsError(res) Then
'no match
Else
For iCtr = LBound(myCols) To UBound(myCols)
If sourceWks.Cells(.Row, myCols(iCtr)).Value _
= LookinWks.Cells(res, myCols(iCtr)).Value Then
'do nothing, they match
Else
oRow = oRow + 1
rptWks.Cells(oRow, "A").Value = myKey
rptWks.Cells(oRow, "B").Value = myCols(iCtr)
rptWks.Cells(oRow, "C").Value _
= sourceWks.Cells(.Row, myCols(iCtr)).Value
rptWks.Cells(oRow, "D").Value _
= LookinWks.Cells(res, myCols(iCtr)).Value
End If
Next iCtr
End If
End With
Next myCell

LookinWks.Parent.Close savechanges:=False
rptWks.UsedRange.Columns.AutoFit

application.screenupdating = true

End Sub


GM wrote:

I am trying to write some code that will allow a user to compare data
columns from two separate sheets based on one or more key columns.
The first sheet comes from some laboratory instruments which generate
data associated with one or more columns making a unique key. The
second sheet is a scrubbed version of the first sheet to send to a
client. It also will have the key columns but their position may
vary. The goal is to make sure the second sheet's data columns match
those of the first.

There is a user form that allows one to select which columns on each
sheet represent the keys and which represent the data.

I currently am using several nested FOR loops which traverse through
each sheet to find the matching keys then compare the data columns.
Differences or duplicates are printed into a separate sheet for
reference.
One caveat is that I am not allowed to make changes to either source
or destination sheet.

Here is my algorithm:

For loop through each destination row
For loop through each source row
For loop through each key column
Compare key column
If key columns don't match then Exit For(go to next source
row)
For loop through each data column (All keys match)
Compare data columns and print problems
Next
Next
Next
Next

It works, but is quite inefficient.

Would there be any suggestions on how I might use any Excel built-in
functions, Range methods or such in order to improve the process?

Thank You!

--

Dave Peterson