Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a list of new data against a database
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a list of new data against a database
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a list of new data against a database
Use one of the lookup functions like Match() to check your new
accounts against the existing ones. Tim. "Kris Aryanto" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a list of new data against a database
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. -- Regards, Tom Ogilvy "Martin French" wrote in message om... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a list of new data against a database
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 "Martin French" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database for checking out books | Excel Discussion (Misc queries) | |||
Question about a unique list in VBA by checking data inside of 2 columns | Excel Discussion (Misc queries) | |||
Retrieving data from a database list | Excel Discussion (Misc queries) | |||
checking if an access database is open from excel macro | Excel Programming | |||
Checking database field | Excel Programming |