Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there a way to run an MS Query for each row in a spreadsheet? For example, I have a sheet that looks like this:

CustomerID
111
222
333

I have the customer's address in a database, and I'd like to annotate each row in the sheet with the customer's city, like so:

CustomerID     City
111            Chicago
222            New York
333            Los Angeles
share|improve this question
    
What format is the database? What version of excel do you use? –  wizlog Oct 6 '11 at 14:00
    
the database is an obscure format, but I can get to it through an ODBC connection. I'm using Excel 2007. –  Chris Curvey Oct 6 '11 at 14:53

1 Answer 1

It would be simpler to do a single query pulling results to another sheet of all CustomerIDs, then use vlookup in a formula to find the city for that CustomerID in those results.

share|improve this answer
    
I thought about that, but I have about 3MM customers. that would take a long time to download and a lot of RAM to store in Excel, no? –  Chris Curvey Oct 6 '11 at 16:06
    
Might I ask, how the list of CustomerIDs are being generated in the first place? Or is dataentry occurring, and you need to lookup the appropriate city at the entry stage? –  datatoo Oct 6 '11 at 16:16
    
an infinite number of monkeys (aka "the finance department") comes up with the spreadsheet via a process that is opaque to me. They just asked me to annotate the sheet. I can make the annotations by writing a Python program, but I thought there might be a clever way to do this directly in Excel. –  Chris Curvey Oct 6 '11 at 17:45
    
for the quantity of CustomerIDs, you are right, pulling it into a sheet to use a vlookup on, won't work, unless you broke the query results into different sections based upon CustomerID ranges, and then adjusted the formula to look at the appropriate one. scripting a query on each individual item would probably be fairly long processing. Python might be a good choice –  datatoo Oct 6 '11 at 21:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.