Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Re: Comparing Two Ranges

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


Comparing Two Ranges... An alternative is...

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<>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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.