Unbeknownst to each other, two of us have been updating a worksheet which has
27000 records and about 10 fields to each record. The changes are not
extensive, but need tobe identified and resolved between the two versions.
The changes could be in any of the rows or columns. I wrote a small VBA
program which ran through 15 minutes for comparing record 1 in Wksheet 1 with
a single column of Wksheet 2. Is there a purely Excel way of identifying the
changes either through coloring cells or using a third spreadsheet? At less
than 15 minutes per column per record. thanks.
--
VSExcel

What you want to do CAN be done in Excel, but only with a great deal of sweat
and pain. If you have a column that should have a unique entry for each row,
i.e., 27 thousand unique entries in that column, then I'd suggest using MS
Access to do the comparison for you.

You would import both Excel files into Access, giving each a unique name,
After you have imported the data, go into design mode on each table and make
the unque column the primary key for that table. Next create a query that
links the two tables on that prmary key column and pulls out each record
where

table1.field1 < table2.field1 OR
table1.field2 < table2.field2 OR
table1.field2 < table2.field3 OR etc

Be sure to use the primary keys only for linking and don't test them for
inequaltiy

If you do this, you will get a result set that contains all the fields from
table 1 plus all the fields for table 2 where a) the key fields match but 2)
one value (or more) between the two sets of fields doesn't match.

Good luck





"VSExcel" wrote:

Unbeknownst to each other, two of us have been updating a worksheet which has
27000 records and about 10 fields to each record. The changes are not
extensive, but need tobe identified and resolved between the two versions.
The changes could be in any of the rows or columns. I wrote a small VBA
program which ran through 15 minutes for comparing record 1 in Wksheet 1 with
a single column of Wksheet 2. Is there a purely Excel way of identifying the
changes either through coloring cells or using a third spreadsheet? At less
than 15 minutes per column per record. thanks.
--
VSExcel

If this is a one time check for changes, and the changes are to the
data (the rows still line up), I would make a third worksheet that
compares every cell in the 10 columns by 27000 rows and puts a 0 if
they match and a 1 if they don't. (e.g. =IF(Sheet2!A1=Sheet3!A1,0,1)
On the new sheet that compaires the two, add a column of numbers
indicating the original row numbers (put a 1 in the first row and fill
down to the bottom) and a column that sums the first 10 columns. That
column will be non zero if any of the cells don't match between the
two sheets that got out of sync. Convert that column to values. You
can then sort by the column that totals the differences. Sort in
decending order and all the rows that don't match will come to the top
with the colmn that shows the original row number identifying the row
numbers in the original sheet that don't match. If one of the sheets
has had a row added or deleted and the rows no longer line up, you
will find that the rows below the insertion or deletion are all
screwed up. You can go to that row, make the necessary correction,
and start the procedure again.

If this is something that is going to happen repetitively, you ought
to go to the vba group and get help writing some code.

Good luck.

Ken
Norfolk, Va



On Feb 5, 5:35 pm, VSExcel wrote:
Unbeknownst to each other, two of us have been updating a worksheet which has
27000 records and about 10 fields to each record. The changes are not
extensive, but need tobe identified and resolved between the two versions.
The changes could be in any of the rows or columns. I wrote a small VBA
program which ran through 15 minutes for comparing record 1 in Wksheet 1 with
a single column of Wksheet 2. Is there a purely Excel way of identifying the
changes either through coloring cells or using a third spreadsheet? At less
than 15 minutes per column per record. thanks.
--
VSExcel