excel help - updating columns

[rh71]

say I have the following data in an XLS

name | date JOINED company
________________________
carl | 5/6/10
dan | 6/4/10
earl | 8/15/10

and someone wants me to add a column (data found in a SQL database) that shows all that contain a 'date LEFT company', but obviously not all have left. So in the XLS, I will populate for only those users listed above and some will not have data in the new column.

The most important thing is to populate this XLS with the new column somehow automatically, matching the users found in a SQL pull for date left company. Is there a way in Excel to say "put [this new column of data] that match the names you find in column A"? I find myself needing to update existing XLS' frequently with new data they want added as a new column.

Right now the only thing I can think of is to throw the entire XLS data into a temp table and update the table with the SQL pull results (I use coldfusion to query then update), then copy the whole temp table of results into an XLS again. The problem with this is some XLS have over 10 columns and many rows and I'm not about to throw all that into a temp table (creating numerous inserts and with various data types) just to add a new single column. Is that the only way?

 

[theknight571]

Will Microsoft Query do what you want?

http://office.microsoft.com/en-us/e...ry-to-retrieve-external-data-HA010099664.aspx

I haven't used it for individual data points but I have for pulling complete query results into a spreadsheet from a MySQL database.

 

[rh71]

Will Microsoft Query do what you want?

http://office.microsoft.com/en-us/e...ry-to-retrieve-external-data-HA010099664.aspx

I haven't used it for individual data points but I have for pulling complete query results into a spreadsheet from a MySQL database.

looks promising... I was able to connect and get the query run, but it's taking forever to insert it (still working after 5 mins but the query should take 5 secs at most). I doubt it will insert it exactly how I want since I'm not sure how to get it to match with existing rows in the XLS. Like the result for Earl goes into Earl's row... etc.

 

[rh71]

EDIT>> think I've got a way now...

- insert names column into temp table with an extra NULL column
- run cfm containing SQL to update temp table with new column data, (this will effectively leave those without results as null)
- copy & paste the temp table back into XLS as new column (this will include the NULLS so the data matches up row by row)

Still somewhat manual but it beats matching rows by hand.