3

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
slhck
  • 223,558
  • 70
  • 607
  • 592
Chris Curvey
  • 1,347
  • 2
  • 13
  • 18

1 Answers1

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.

datatoo
  • 3,440
  • 20
  • 31
  • 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 06 '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 06 '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 06 '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 06 '11 at 21:28