Joins in excel

[fralo]

Hey guys, I come from a SQL background. I've never really worked with Excel that much. I basically have two tabs in an excel spreadsheet. I need to create a third one which is simply a 'join' between the other two where two fields in one sheet is equivalent to the same two in the other sheet.

This may be a very elementary question to some of you. Any help would be appreciated.

Thank you.

[martindwilson]

the simplest way is to
open the new 3rd sheet
type = in a1 (dont hit cr or enter)
click tab of your first sheet
click first cell of column you want to copy
hit return
now if you click in new third sheet a1 you will see how excel references sheet 1
you can just click/drag to fill down.
repeat for columns from sheet 2

[NBVC]

where two fields in one sheet is equivalent to the same two in the other sheet

What exactly do you mean with this? Can you give examples?

[fralo]

Perhaps I didn't explain this well enough. Excuse me. I don't want to make a third sheet which is simply a combining of everything in the first two. I only want to bring over rows where certain field values in sheet 1 are equal to those in sheet 2.

In other words, where sheet1.A1 = sheet2.A1 and sheet1.B1 = sheet2.B2

I hope this makes a little more sense.

[Ron Coderre]

You might be able to use MS Query to extract Excel ranges from your
two wkshts. This works for consolidating data from the active workbook (Just save it first so Excel can find it):

This example uses 2 named ranges in the current workbook.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
The data in each sheet is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.

The data in each sheet is in named ranges.
--->I used rngSht1Data and rngSht2Data

(Note: MS Query may display warnings about it's ability to show the query
...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the extracted data to start

2)<Data><Import External Data><New Database Query>
• Databases: Excel Files

Browse to the current file, pick the ONE range to import.
--->Accept defaults until the next step.

At The last screen select the <View data/Edit the Query> option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT
 rngSht1Data.Dept,
 rngSht1Data.PartNum,
 rngSht2Data.Desc,
 rngSht2Data.Price
FROM
 `C:\MyWkbk`.rngSht1Data rngSht1Data, 
 `C:\MyWkbk`.rngSht1Data rngSht2Data
WHERE
 rngSht1Data.Dept =  rngSht2Data.Dept AND 
 rngSht1Data.PartNum =  rngSht2Data.PartNum
 
Note: Apostrophes in the SQL code ( ` )are located on the tilde key (~)

Return the data to Excel.

Once that is done....to get the latest data just:
Right Click in the data range
...<Refresh Data>

You can edit the query SQL at any time to
add/remove data sources and/or fields.

Is that something you can work with?

[fralo]

Actually, I stumbled across an Add-in tool called DigDB while googling. It allows, among others, to do joins across Excel tables. It works perfectly.

Thanks for all of your help.