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:


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
  • 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. Oct 6 '11 at 14:53

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.

  • 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? 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. 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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