Merge two spreadsheets based on a common column
There has to be a simple way to do this either in Excel or SQL Server 2005.
I have one spreadsheet with customer info including the primary key serv_id, I have another spreadsheet with two columns, serv_id and usage. I want to add the usage to the first spreadsheet based on the serv_id. sql something like this.. update table.usage where table.serv_id = spreadsheet.serv_id.
Eventually I'm going to put this in a sql table, not sure if I should do the merge before I put them in sql server or after.
What's the best way to do this?
I have one spreadsheet with customer info including the primary key serv_id, I have another spreadsheet with two columns, serv_id and usage. I want to add the usage to the first spreadsheet based on the serv_id. sql something like this.. update table.usage where table.serv_id = spreadsheet.serv_id.
Eventually I'm going to put this in a sql table, not sure if I should do the merge before I put them in sql server or after.
What's the best way to do this?
Hi, You could use the vlookup function in excel. It is used to find a matching value from a list and return data from that row. The screenshot below give an example, Ive put both set of data on the same page for simplicity. Once youve done this you would need to copy the data and paste special the resulting data as values to fix it, then clear any #N/A entries using find/replace.
vlookup.JPG
vlookup.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is what I did and it seems to have worked. I did a left join, have to do it for each month usage for 3 years, but it's doing the trick.
Hi,
Post your query in another question, as that also sounds painful, and I think I can help optimise it for you.
Cheers
David
Of course there are others that can help as well :-)
Post your query in another question, as that also sounds painful, and I think I can help optimise it for you.
Cheers
David
Of course there are others that can help as well :-)
You can check this site, where you can find more info. If found it very useful, but I was using Excel files from a VB program. I don't know how are you planning to deal with them. I assume that you could set from SQL Server a connection to Excel files and merge them in a third one.
http://www.xtremevbtalk.com/showthread.php?t=217783