Checking a list of new data against a database

[Martin French]

Hi
I have a database in Excel of existing customer account numbers. I
also have a list of new account numbers. I want to be able to check
the new list against the existing list and create two new list - one
list of new accounts already on the database, and another list of new
accounts not already on the database.
Does anybody know of a quick and simpe way of doing this in vba code?

Many thanks

Martin

 

[Guest]

Martin:

I know an app that can help assist you on this. You can download it from
http://www.tairasoft.com/download/DataMinerBeta.zip

I use this tool to extract certain portion of the data from the worksheets.
The initial step requires you to convert each of your data worksheet into csv
file. After that the process is quite simple.

If you are planning to use this tool then you need to import two of your
data lists. From that point you can run a simple query to both tables. You
can get the result in which list of new accounts are not already on the
database and are in the database.

Regards,
-Kris

 

[Tim Williams]

Use one of the lookup functions like Match() to check your new
accounts against the existing ones.

Tim.

 

[Tom Ogilvy]

See Chip Pearson's page on unique and duplicates.
http://www.cpearson.com/excel/duplicat.htm

You should be able to cobble something together with this.

 

[Guest]

One way:
Name 2 ranges on your wsh
Acnts and NewAcnts
Create, if needed, a blank column to the right of Acnts

Sub FindDuplicates()
Dim oCell As Range
Dim x

For Each oCell In Range("NewAcnts")
x = Application.VLookup(oCell, Range("Acnts"), 1, False)
If Not IsError(x) Then
oCell.Offset(0, 1) = "Found"
End If
x = ""
Next oCell
End Sub

You could then filter you Acnts list for Found