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
Chris Curvey
Posted 2011-10-06T11:39:37.660
Reputation: 1 105
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
Posted 2011-10-06T11:39:37.660
Reputation: 3 162
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 – 2011-10-06T16:06:41.447
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 – 2011-10-06T16:16:15.357
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 – 2011-10-06T17:45:45.620
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 – 2011-10-06T21:28:08.300
What format is the database? What version of excel do you use? – wizlog – 2011-10-06T14:00:37.553
the database is an obscure format, but I can get to it through an ODBC connection. I'm using Excel 2007. – Chris Curvey – 2011-10-06T14:53:56.177