Spreadsheet 1 has company info. Spreadsheet 2 has contact info. On both spreadsheets I have an ID# that is the same for the company as the contacts who work for that company.
Now I want to build a report that lists the contacts for each company.
- Follow via:
- RSS
- Email Alert
Question
0
Votes
How do I combine 2 spreadsheets that have 1 field in common?
25th Aug 2010
Answers (3)
0
Votes
Try this
Are you sure that you have exactly one contact record for each company?
If not, then have a look at relational databases, for instance Access.
You can use lookup functions.
If the index value your searching for is in cell G1, the other list has the index value in column A and the value you want to copy in column C
=LOOKUP(G3;$A$2:A7;$C$2:$C$7)
The dollar signs are needed to not have the lookup and result vectors "slide" with your function when you copy it.
Same formula if you're building on a different sheet.
=LOOKUP(A1;Sheet1!$A$2:$A$6;Sheet1!$C$2:$C$6)
Better than "dollar" addresses is to use named ranges.
If not, then have a look at relational databases, for instance Access.
You can use lookup functions.
If the index value your searching for is in cell G1, the other list has the index value in column A and the value you want to copy in column C
=LOOKUP(G3;$A$2:A7;$C$2:$C$7)
The dollar signs are needed to not have the lookup and result vectors "slide" with your function when you copy it.
Same formula if you're building on a different sheet.
=LOOKUP(A1;Sheet1!$A$2:$A$6;Sheet1!$C$2:$C$6)
Better than "dollar" addresses is to use named ranges.
25th Aug 2010
Replies
When you hand over this to someone else, don't leave these formulas in. It only takes someone smart enough to delete the source data sheet, and they'll be left with just a bunch of errors.
When you have the view ready, copy it, then, in a fresh data sheet, do Paste_Special-Values.
(In Excel 2003, this is in the edit menu. I have no clue where it's hidden in 2007.)
When you have the view ready, copy it, then, in a fresh data sheet, do Paste_Special-Values.
(In Excel 2003, this is in the edit menu. I have no clue where it's hidden in 2007.)
TobiF
25th Aug 2010
it's on the Home Tab, on the Clipboard (far left) under Paste.
stephanisat_z
26th Aug 2010
0
Votes
Use Access or LOOKUP
If this is going to be an ongoing report or if the data is going to be continuously updated, I actually recommend you import them into Access and link the fields. Creating a report is far easier in Access.
If you have to stay in Excel, you have a couple of options. If each spreasheet has one workbook, copy them so that they are in the same spreadsheet (alternatively link the other spreasheet). Depending on the type of report that you are trying to build, you can create a lookup table of the company list as long as the key is listed in alpha-numeric order (your registration key). On the report page, use the LOOKUP command to associate the data by selecting the customer and use LOOKUP to find the associated company.
If you have to stay in Excel, you have a couple of options. If each spreasheet has one workbook, copy them so that they are in the same spreadsheet (alternatively link the other spreasheet). Depending on the type of report that you are trying to build, you can create a lookup table of the company list as long as the key is listed in alpha-numeric order (your registration key). On the report page, use the LOOKUP command to associate the data by selecting the customer and use LOOKUP to find the associated company.
25th Aug 2010
Replies
http://databases.about.com/od/tutorials/ss/exceltoaccess.htm
This looks like a good tutorial on converting excel sheets into access dbases.
This looks like a good tutorial on converting excel sheets into access dbases.
NexS
25th Aug 2010
0
Votes
One possible suggestion...
I personally, would create a Third Sheet. Using the tips given above, to create a SQL table and using the data from the #1 sheet and the #2 sheet to draw from for populating the new sheet. That way, any changes or up-dates, to either (#1 or #2) will automatically up-date you new sheet. but will not affect either of the others. Also, that way you can choose exactly what will be shown on the new sheet.
Updated - 26th Aug 2010
Replies
How do you in excel issue the SELECT ... WHERE statement?
What names do you use for worksheets and columns?
What names do you use for worksheets and columns?
TobiF
26th Aug 2010
First on your last point; The naming would be up to the first (original) poster for this thread.
Second, I don't use "Excel" if I can help it. I do however have to use it from time to time. (I, myself, use Star Office /Open Office, much more popular here in Europe) But in general, the use of 'Hyper Links' with-in the cells would function fine. That way in put from any of the already in use tables would automatically up-date the 'New' third sheet The poster wanted to create.
No?
Second, I don't use "Excel" if I can help it. I do however have to use it from time to time. (I, myself, use Star Office /Open Office, much more popular here in Europe) But in general, the use of 'Hyper Links' with-in the cells would function fine. That way in put from any of the already in use tables would automatically up-date the 'New' third sheet The poster wanted to create.
No?
dawgit
29th Aug 2010
Both answer 1 and 2 in this thread indicate that for this type of task, a relational db, for instance Access, could be a better choice.
Then you proposed that we're going to extract data from spreadsheets using SQL. That's were I got stuck. I have been using SQL to import data to Excel from an Oracle database, but I have never seen SQL as a tool to retrieve data from a spreadsheet.
Even in OpenOffice, one would have to copy or link the data from Calc to Base in order to perform thesse tasks.
Regarding my last question, I was just curios how one in a typical SQL statment would refer to rows and columns in a spreadsheet, but I believe the simple answer to this is: "You don't".
Then you proposed that we're going to extract data from spreadsheets using SQL. That's were I got stuck. I have been using SQL to import data to Excel from an Oracle database, but I have never seen SQL as a tool to retrieve data from a spreadsheet.
Even in OpenOffice, one would have to copy or link the data from Calc to Base in order to perform thesse tasks.
Regarding my last question, I was just curios how one in a typical SQL statment would refer to rows and columns in a spreadsheet, but I believe the simple answer to this is: "You don't".
TobiF
29th Aug 2010
One could do the opposite however... Include the statement in the spreadsheet document from the SQL (of choice). That would give the Poster the required results I think he's looking for. Maybe.
dawgit
31st Aug 2010
.
TobiF
31st Aug 2010
