LOOKUP function

Charles,

Do you have Access available to you? If you do I can walk you through using it to solve your problem. What you want to do is what databases do routinely. To do it in Excel would be more trouble, IMO, than its worth.

Bariloche

Yes I do have Access available to me. Any assistance would be greatly appreciated.

Charles

Charles,

: I am exporting data from two different sources into excel. I am putting both exported spreadsheets into the same workbook. The two exported worksheets only have one data field in common. The field in common is company name (which is text). I am trying to create a third worksheet (within the workbook)that would automatically retrieve data from the two exported worksheets. That way I would have one report with all the data. I've tried to use the LOOKUP function by copying the company names into the third sheet and then have it look up in the other sheet for the data but the results I am getting back are inaccurate. Am I approaching this problem correctly? Cut and pasting won't work easily since several company names are missing from one of the exported worksheets. Anyway, I rather have the process more automated.

phew

Charles,

Sorry for the delay in getting back with you. I didn't notice your reply to my post. Here's what we need to do to get you taken care of:

First, make sure your column headings don't contain any special characters or spaces. Don't use things like % or & or * in the column names (field headings, in Access-speak). For example, the company name field should be called something like "CompanyName" (without the quotes, of course), or "Company_Name".

Save the workbook and close it. Open Access and create a new database, call it whatever you like.

To import a table into Access, click on the "Table" object or tab (if you're not already there), and right click somewhere in the blank space. You should get a menu up that has "Import..." as one of the options. Click on it, change the "Files of type:" to Excel and navigate to where you stored your workbook and double click on the file.

This should start the spreadsheet import wizard. Select your the worksheet that has one of your tables on it, click Next and check "First Row Contains Column Headings" and click Next. Store it in a new table, click Next, click Next, indicate "No Primary Key" and click Next and give the table a name. I usefully use a convention called "Hungarian" which uses prefixes to indicate object types. In this case I would call it tblWhatever. You can use that convention if you like, but whatever you do don't put spaces or special characters in the name. It will handle spaces (doesn't create an error) but you're better off in the long run to not put in spaces.

Repeat the process for the other table as well. But make sure you don't append it to the existing table. Where the wizard asks "Where do you want to store this?" just keep the radio button "In a new table" indicated.

If you imported any blank rows or columns, you'll want to go into the tables and delete those. Also, you might want look at the design of the table and make sure that fields have the correct data type (when you're in the table there's a button that has a picture that looks like a ruler a pencil and a triangle, click that to get into design mode.)

Assuming that everything's alright, click on the Query object or tab. We want to create a new query and we want to use design mode, not the query wizard. When the Show Tables dialog comes up double click on each of your tables to add them to the table pane of the query design window (I know this is all probably pretty foreign to you, but stick with it, hopefully it will get you to where you want to be :-) ). Once you have your two tables added you can close that dialog box.

Now we have to join the two tables. (You can resize or maximize stuff so that you can see things better. You want to be able to see the table pane and the query grid.)

Left click on the CompanyName field in the table on the left and drag and drop it on the CompanyName field in the table on the right. If you've done this successfully you should now have a line that "joins" the two tables. This provides the relationship between the two tables. It basically says that Table1.CompanyName = Table2.CompanyName.

If you right click on the join line you can access a dialog box so that you can change the join type. We might have to do that later.

We haven't covered what kind of data you have yet. When messing around with queries you want to know your data pretty well. This is so that when you get a query result if its not correct you can tell just by looking at it.

For now lets just add the fields into the query grid and see what happens (I'm at a bit of a disadvantage here 'cuz I don't know what to expect from your data.) Put all the fields in and see what happens. You put fiels in by either double clicking on the field up in the table pane, or dragging a field from the table pane down to the grid. If you want all of the fields, you can double click on the table title bar, all the fields will be hi-lited and you can drag them all down at once.

I know this seems like a lot of stuff (believe me its a lot of typing LOL) but after awhile this stuff becomes second nature and you can just zip through it.

For now though, just get this far, play around with the query a little bit, maybe read some of the help stuff in Access and then repost with your questions. I'll also need to know a little about your data. For example, are the company names repeated in either table? Both?

Good luck. And remember, post back when you get through this.

enjoy

Charles, : : Do you have Access available to you? If you do I can walk you through using it to solve your problem. What you want to do is what databases do routinely. To do it in Excel would be more trouble, IMO, than its worth.

: