Desktop·25,135 discussions

How do I combine 2 spreadsheets that have 1 field in common?

Locked

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.

Topic

Clarifications

In reply to How do I combine 2 spreadsheets that have 1 field in common?

Clarifications

Try this

In reply to How do I combine 2 spreadsheets that have 1 field in common?

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.

One more thing

In reply to Try this

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.)

In 2007…

In reply to One more thing

it’s on the Home Tab, on the Clipboard (far left) under Paste.

Use Access or LOOKUP

In reply to How do I combine 2 spreadsheets that have 1 field in common?

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.

Moving to access. . .

In reply to Use Access or LOOKUP

http://databases.about.com/od/tutorials/ss/exceltoaccess.htm

This looks like a good tutorial on converting excel sheets into access dbases.

One possible suggestion…

In reply to How do I combine 2 spreadsheets that have 1 field in common?

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.

In Excel???

In reply to One possible suggestion…

How do you in excel issue the SELECT … WHERE statement?

What names do you use for worksheets and columns?

hummm….. well,

In reply to In Excel???

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?

Hehe

In reply to hummm….. well,

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”.

True, but…

In reply to Hehe

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.

Or in any other app that has a client for SQL…

In reply to True, but…

.

[Author]

Replies