Combining data from two sheets/workbooks based on common field/column

[barrydow]

I've got a couple of sheets that I need to combine at least some info from. In both of those sheets/workbooks (each sheet is a separate workbook and/or .CSV or .XLS file) there is a column that I'll label here as "Key field". In one of the sheets I expect I'll have somewhere over 2,000 rows of potential matches for the same key field item that is reported about in the second sheet. In the second sheet, I expect I'll have something like 200 - 300 (for now) rows of data, so obviously I will be lacking matches for many rows when I look at the data.

A sample would look like this:
(Sheet 1)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Key Field[/TD]
[TD]Column Two[/TD]
[TD]Column Three[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Building[/TD]
[TD]Room[/TD]
[/TR]
[TR]
[TD]1234568[/TD]
[TD]Building 2[/TD]
[TD]Room 2[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]1234569[/TD]
[TD]Building 3[/TD]
[TD]Room 3[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


(Sheet 2)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Key Field[/TD]
[/TR]
[TR]
[TD]Some data[/TD]
[TD]Some data[/TD]
[TD]{ null }[/TD]
[/TR]
[TR]
[TD]Some data row 2[/TD]
[TD]Some data col 2 row 2[/TD]
[TD]1234569[/TD]
[/TR]
[TR]
[TD]Some data r3[/TD]
[TD]Some data c2r3[/TD]
[TD]{ null }[/TD]
[/TR]
[TR]
[TD]Some data r4[/TD]
[TD]Some data c2r4[/TD]
[TD]1234567[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


I should have a match within the first sheet for every key field value (non null) in the second sheet but not the inverse (there may not be a match for every key field value in the first sheet as it may list a lot more records than are available in the second sheet.)
I can sort the sheets if necessary, ordered by the key field, to help make it easier, but as in the sample above, the original data may not be sorted in that manner.

My long term goal is that I'd like to generate a third sheet that has a couple of columns - first the key field column, and second a combination of the second column and third column (or whatever other columns those may be as I may have other columns that I'd be skipping over) in the second column of the new sheet.

This all revolves around property data (computer hardware property) that is id'd by the key field, and is tracked to a location that is the combo of the sample columns shown (a building name and room number). So I'd be concatenating the building+room into the result in the new sheet.

Hopefully this all makes sense and is something some of the nice folks here can help with. Definitely looking forward to learning some tips that would help make this task go as smoothly as possible.