Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining databases
How can I combine two excel spreadsheets? One contains about 300 names and
addresses. The other contains about 1000 names and other data for those names. The 300 names from the first spreadsheet are among the 1000 in the second. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining databases
Christine,
You can use the brute-force =VLOOKUP command. 1. Sort the 1000 record database according to whatever matches in the 300 record database. Let's say it is 'NAME'. Make sure NAME is the first column of the 1000 record database. Let's also assume NAME is the first column of the 300 record database. Both databases start in column A. 2. Let's say you need the value of 'PHONE', and 'PHONE' is the... sixth column of the 1000 record database. 3. The 300 record database is in Sheet1, and the 1000 recird database is in Sheet2 4. Use this function: =vlookup(A1,Sheet2!$a$1:$J$1000,6,FALSE), whe A1 = the cell containing the match between the two databases. Sheet2!$a$1:$J$1000 = The range of the second database. 6 = I want the value in the sixth column FALSE = Return an error if I do not find a matching record. Cheers! --Dan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining databases
Hi,
For exact matches, which is what you are doing, there is not need to short the data. The lookup column in the second table must be to the left of the data you want returned. That is Name must be to the left of Zip Code. VLOOKUP only looks to the right, for other situations we would use MATCH and OFFSET or INDEX. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Dan" wrote: Christine, You can use the brute-force =VLOOKUP command. 1. Sort the 1000 record database according to whatever matches in the 300 record database. Let's say it is 'NAME'. Make sure NAME is the first column of the 1000 record database. Let's also assume NAME is the first column of the 300 record database. Both databases start in column A. 2. Let's say you need the value of 'PHONE', and 'PHONE' is the... sixth column of the 1000 record database. 3. The 300 record database is in Sheet1, and the 1000 recird database is in Sheet2 4. Use this function: =vlookup(A1,Sheet2!$a$1:$J$1000,6,FALSE), whe A1 = the cell containing the match between the two databases. Sheet2!$a$1:$J$1000 = The range of the second database. 6 = I want the value in the sixth column FALSE = Return an error if I do not find a matching record. Cheers! --Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
databases | Excel Worksheet Functions | |||
what is a feild in databases? | Excel Discussion (Misc queries) | |||
Merging Databases | Excel Worksheet Functions | |||
Databases | Excel Discussion (Misc queries) | |||
DATABASES | Excel Discussion (Misc queries) |