In previous versions of Excel there was an add-in making it possible to
compare two databases. The shortcoming of that add-in was, however, that the
two databases had to be sorted exactly alike. My problem is to find out if
any item in the second database has changed, been deleted or if there are
new items. Values could be both text and numbers. I have tried Look-up and
Match-functions with a loop, but still cannot figure out how to do this.
Does anyone out there know about a solution to this?

Stefan

This is really a job for Access. But, I've done it in Excel a
follows:

I assume there is something unique about each record, such as custome
name, product name, sales territory.

I create a new sheet, which I name "temp".

On the new sheet, I create two lists ... one from each of the tw
databases you want to compare. Each list is a concatenation of th
unique identifiers

... "customer name" & "product name" & "sales territory"

then, I do a Match(list2, ThisItemInList1, 0)
to make this really robust, I use
MatchRow = if(iserror(Match(list2, ThisItemInList1
0)),0,Match(list2, ThisItemInList1, 0))

If MatchRow 0 then
For j = 1 to LastColumnInDatabase
With Sheets("Database2").Cells(MatchRow, j)
If Sheets("Database1").Cells(RowOfThisItemInList1,j). Text <
.Text then .Interior.Color = vbYellow
End With
Next j

After doing all of the comparisons, I run another macro that hides al
rows where there is no highlighted cells

then, delete the temp shee

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Hi and thanks!
I had an idea about Access and tried some, as I thought intelligent tricks,
but with no success, sorry to say. But, if you have a an Access solution I
would be more than happy to know about it. I use Access a lot too. Please,
let me know if you sit on a smart Access solution!

Regards
Stefan

"MSP77079" skrev i meddelandet
...

This is really a job for Access. But, I've done it in Excel as
follows:

I assume there is something unique about each record, such as customer
name, product name, sales territory.

I create a new sheet, which I name "temp".

On the new sheet, I create two lists ... one from each of the two
databases you want to compare. Each list is a concatenation of the
unique identifiers

.. "customer name" & "product name" & "sales territory"

then, I do a Match(list2, ThisItemInList1, 0)
to make this really robust, I use
MatchRow = if(iserror(Match(list2, ThisItemInList1,
0)),0,Match(list2, ThisItemInList1, 0))

If MatchRow 0 then
For j = 1 to LastColumnInDatabase
With Sheets("Database2").Cells(MatchRow, j)
If Sheets("Database1").Cells(RowOfThisItemInList1,j). Text <
Text then .Interior.Color = vbYellow
End With
Next j

After doing all of the comparisons, I run another macro that hides all
rows where there is no highlighted cells

then, delete the temp sheet


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/