Excel / sql / vlookup question?

Question

I have a sql table with a lot of records, say 120,000, so it's too large to go into excel. I have another table that has two columns, the first column has an account name and the second column has the group name. I want to do a search within the sql table to compare it to my alt. table and in instances where things can be grouped return the group name, otherwise leave the name as is. If the table would fit into excel I could use a vlookup equation like this:

IF(ISERROR(VLOOKUP(C3,I:M
,2)),C3,VLOOKUP(C3,I:M,2))

but it won't fit into excel. Any ideas on how I could apply something like this to a sql table?

Answer 1

i see two solutions:
1) You definitly need to use macros to conect to the SQL or
2) go to menu data / import import external data / new data query... Follow the query wizard, make sure you just select the information you need (Account name and group)..
Right at the end of the query wizard select the option to view or edit query in microsoft query and click finish

Here (MS query) is where you can do the trick to bring unique records. Say you have column AccountName and Group... add another column with the same field "AccountName" ... Having this last column active, click the sum symbol (E) on the top. It will transform that columnin to "count of AccountName"

go menu File / return data to microsoft excel... make sure you place your query in a separate sheet.

Hope this works for you.. if need further help feel free to send me an email... [e-mail]

Answer 2

You have to use VBA (macro's) for Excel. In otherwords, write a macro.

I have a lot of VBA applications in Excel that connect with Access databases using DAO (which is obsolete), but I think ADO is still supported.

Check out ADO on Microsoft's VBA application developers web pages -- I'm sure there are ways to open up the SQL database and do what you want to do.
.