Compare databases

  • Thread starter Thread starter Stefan Jansson
  • Start date Start date
S

Stefan Jansson

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