Comparing Two Ranges

Posted by George Clements on August 10, 2001 9:17 AM

I have a column of data in workbook A and another column of data in workbook B. Each column is 55,000 rows deep with data - text or numbers.

I want to compare the column in workbook A with the column in workbook B and identify which cells contain different data for the same row location.

I need to do this very quickly. I have written code that loops through each cell in one column and compares it with the cell at the same row location in the other column. The trouble is it isn't fast enough.

I have found the VBA "RowDifferences" method but I can not seem to make it work when more than one worksheet is involved.

Does anyone know how to solve my problem

Thanks

George

Posted by Cory on August 10, 2001 9:37 AM

Have you tried just using a 'if' formula instead? Say you want to compare A's column with B's column. Data in both workbooks is in column A.
In workbook A go to cell B1 and type in:

=IF(A1=[testb.xls]Sheet1!A1,"","Different")

Now just copy that down as far as you want and where they're the same, you'll see nothing, but where they're different, you'll see "Different"...

That help?

Cory

Posted by Aladin Akyurek on August 13, 2001 6:08 AM

to use Conditional Formatting. I'll assume that the data in A runs from A1 to A550000.

Select this range.
Activate Format|Conditional Formatting.
Select "Formula is" on the Conditional Formatting dialog.
Enter as formula: =A1&LT;&GT;B1
Activate Format.
Select Red as background color on the Patterns tab.
Activate OK.

The A cells which are red colored will give you rows that are different.

Aladin