Excel 2010 - combining spreadsheets
October 28, 2013 4:16 PM Subscribe
How do I combine two Excel spreadsheets that have one common field? I know what I want the end result to be, but I don't know what the solution is called or how to get there.
Spreadsheet 1 contains sales id, customer name, products sold, etc. Spreadsheet 2 contains sales id and sales team name and position. The common field is sales id, but the number of products and sales teams varies for each sales id number. I'll need to combine these reports on a regular basis, so a solution that is quick and easy to duplicate would be very helpful. My skill level is just a bit past that of a beginner.
Spreadsheet 1 contains sales id, customer name, products sold, etc. Spreadsheet 2 contains sales id and sales team name and position. The common field is sales id, but the number of products and sales teams varies for each sales id number. I'll need to combine these reports on a regular basis, so a solution that is quick and easy to duplicate would be very helpful. My skill level is just a bit past that of a beginner.
You could also make column headers on report 1 for each of the fields unique to report 2. Paste all the salesIDs from report 2 on report 1 and then paste the report 2 data into the combined report. Throw it all into a pivot table and it will combine and sum up the information.
posted by munchingzombie at 4:25 PM on October 28, 2013 [1 favorite]
posted by munchingzombie at 4:25 PM on October 28, 2013 [1 favorite]
An index or index match strategy might be more helpful than a lookup. Lookups can fail if they don't like your sort order.
Again, tutorials online.
posted by Lesser Shrew at 4:55 PM on October 28, 2013
Again, tutorials online.
posted by Lesser Shrew at 4:55 PM on October 28, 2013
vlookup or hlookup would work if all you want is to attach data from spreadsheet 2 to spreadsheet 1. if you're going to be doing this on a regular basis and you want the flexibility to do something more with the data, i'd suggest putting a little effort into it and putting it all in Access. it will give your more flexibility to make reports summarized by team or product, for example.
posted by cupcake1337 at 8:17 PM on October 28, 2013
posted by cupcake1337 at 8:17 PM on October 28, 2013
There comes a point when the functionality expected of a spreadsheet fits comfortably into what we old hands call a database. Combine that with "skill level is just a bit past that of a beginner" and we have what we call "job security" (I wish!). You are talking about a join, I am not sure xLOOKUP has the same semantics.
If the spreadsheets are separate files (or even worksheets), maintained separately then you are opening up a huge can of worms, data consistency being the most important consideration. The least problem you should think about is what to do with mismatched rows, i.e. sales id which is in one spreadsheet not the other. Then think about duplicates. Then thinking about moving eveything into Access.
posted by epo at 3:53 AM on October 29, 2013
If the spreadsheets are separate files (or even worksheets), maintained separately then you are opening up a huge can of worms, data consistency being the most important consideration. The least problem you should think about is what to do with mismatched rows, i.e. sales id which is in one spreadsheet not the other. Then think about duplicates. Then thinking about moving eveything into Access.
posted by epo at 3:53 AM on October 29, 2013
ACCESS ACCESS ACCESS. It makes everything so easy. And it really is relatively easy to learn the basics.
posted by MrsMGH at 8:06 AM on October 29, 2013
posted by MrsMGH at 8:06 AM on October 29, 2013
You don't have to use Access, but it may make things easier. I think the determining factors are how often you have to produce the report (more often means Access will work better) and how often the two spreadsheets change (more often means Excel will work better).
Access will work better because you can write a query that will produce the combined report and you will only have to do this once. You can update the tables (spreadsheets imported into Access become tables) as much as you want and then run the query again and you will have the new combined report. Access doesn't come with all versions of Office, either.
Excel will work better if you have tons of updates to the spreadsheets and only an occasional need to produce a new report. You can't just paste a line from Excel into Access; you have to import the data. Importing isn't a big deal, but it can be tricky if you have existing records, are worried about duplicates or just need to update a single line.
Normally I would say you need VLOOKUP as well, but you are dealing with multiple products and multiple sales teams. So, how do you want a single record to look: One product with all sales teams or One sales team with all products? This is known as a many-to-many relationship and is not easily solved by a lookup formula since they only pull one record at their most basic level. If you can change the layout so that all data for one sales team is on a single line, a lookup formula could work since it only needs to pull that one line.
posted by soelo at 9:08 AM on October 29, 2013
Access will work better because you can write a query that will produce the combined report and you will only have to do this once. You can update the tables (spreadsheets imported into Access become tables) as much as you want and then run the query again and you will have the new combined report. Access doesn't come with all versions of Office, either.
Excel will work better if you have tons of updates to the spreadsheets and only an occasional need to produce a new report. You can't just paste a line from Excel into Access; you have to import the data. Importing isn't a big deal, but it can be tricky if you have existing records, are worried about duplicates or just need to update a single line.
Normally I would say you need VLOOKUP as well, but you are dealing with multiple products and multiple sales teams. So, how do you want a single record to look: One product with all sales teams or One sales team with all products? This is known as a many-to-many relationship and is not easily solved by a lookup formula since they only pull one record at their most basic level. If you can change the layout so that all data for one sales team is on a single line, a lookup formula could work since it only needs to pull that one line.
posted by soelo at 9:08 AM on October 29, 2013
Some clarification: You can manually edit table records in Access and you can manually make new ones, it just isn't as simple as it is with Excel. It depends how you get the new data. Is it already in Excel?
posted by soelo at 9:10 AM on October 29, 2013
posted by soelo at 9:10 AM on October 29, 2013
Response by poster: @soelo, the info is pulled from SFDC into two Excel spreadsheets. The goal is to see the products sold and the account team for each opportunity, which has it's own deal id. A template for this type of report does not exist in our version of SFDC. I'll have to pull 2 reports for 3 regions every month and combine the data so that my team can see the opportunities w/ products sold along with account teams.
I want to see:
Opportunity: Feline Shoe Store Set-Up
Products Sold: signs, cash registers, displays
Account Team: AE: Joe Fox, AE2: Andy Cow; AE3: Mary Python
posted by dtp at 6:22 PM on October 29, 2013
I want to see:
Opportunity: Feline Shoe Store Set-Up
Products Sold: signs, cash registers, displays
Account Team: AE: Joe Fox, AE2: Andy Cow; AE3: Mary Python
posted by dtp at 6:22 PM on October 29, 2013
Okay, I think your first step should be making a pivot table for each spreadsheet that gives you a single record per opportunity that lists all data on one line (products for the first, salespeople for the second). This may be the hardest part. Get familiar with pivot tables and then play with them by copying the entire sheet and then Pasting Special Values over it. That will help you get all sales people for a single opportunity on a single line that includes the Opportunity. Then you can use these records to do your lookup formula. Actually, if you have the same opportunities on both sheets and they are sorted the same, you could just copy and paste.
The thing about automation is that you can get tripped up trying to make everything happen automatically. Often a little manual work at either end will make it easier to write the automated part so that it is flexible enough to handle different data sets.
posted by soelo at 1:41 PM on October 30, 2013
The thing about automation is that you can get tripped up trying to make everything happen automatically. Often a little manual work at either end will make it easier to write the automated part so that it is flexible enough to handle different data sets.
posted by soelo at 1:41 PM on October 30, 2013
This thread is closed to new comments.
posted by supercres at 4:18 PM on October 28, 2013 [1 favorite]