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

barrydow

New Member
Joined
Feb 21, 2018
Messages
1
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)
Key FieldColumn TwoColumn Three
1234567BuildingRoom
1234568Building 2Room 2
.........
1234569Building 3Room 3
.........

<tbody>
</tbody>


(Sheet 2)
Column 1Column 2Key Field
Some dataSome data{ null }
Some data row 2Some data col 2 row 21234569
Some data r3Some data c2r3{ null }
Some data r4Some data c2r41234567
.........

<tbody>
</tbody>


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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top