How to carry over the values of a column in 1 workbook to another?

[exceltm]

I have 2 separate workbooks. I want to carry/copy one column in workbook A to workbook B, but only carry/copy over that one column, the values in the "status" column. Since some employees are removed and some are added, I can't simply copy and paste the entire "status" column from workbook A to B. I want to use column A as the unique identifier, then copy the values in the status column from workbook A to B, but to copy only for the employees that still exist in workbook 2. From workbook 1 to 2, employee number 13 is not longer present, so that entire row for employee 13 shouldn't carry over into workbook 2. I've highlighted in red what needs to be carried/copied over from workbook 1 to 2.

For example, here is the sample dataset from the 2 workbooks:

workbook 1
employee	CITY	State	SALES	status
8	DALLAS	TX	$30,835	1
12	SHREVEPORT	LA	$71,638
13	METAIRIE	LA	$245,013	3
19	HARTFORD	CT	$181,049
20	BATON ROUGE	LA	$2,166,385	1
23	OMAHA	NE	$24,132	m
29	WATERBURY	CT	$937,589
33	JACKSONVILLE	FL	$21,980	1
35	OMAHA	NE	$3,066,541
49	WATERBURY	CT	$240,774	m
workbook 2
employee	CITY	State	SALES	status
8	DALLAS	TX	$42,184
12	SHREVEPORT	LA	$47,845
19	Hartford	CT	$252,426
20	BATON ROUGE	LA	$3,332,213
23	OMAHA	NE	$34,241
29	WATERBURY	CT	$1,402,102
33	JACKSONVILLE	FL	$28,294
35	OMAHA	NE	$4,839,064
49	WATERBURY	CT	$487,642
55	New Haven	CT	$33,670

<colgroup><col style="mso-width-source:userset;mso-width-alt:3185;width:67pt" width="90"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:3384;width:71pt" width="95"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

 

[Fluff]

How about

Sub exceltm()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Cl As Range
   
   Set Ws1 = Workbook("Book1.xlsm").Sheets("sheet1")
   Set Ws2 = Workbook("Book2.xlsm").Sheets("sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 4).Value
      Next Cl
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 4).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

Change Workbook & worksheet names to suit.
Both workbooks need to be open.

 

[sandy666]

You can use PowerQuery
Merge table from workbook2 to table from workbook1 by employee then expand Table column in result QueryTable with status only, then load to sheet

workbook1 can be opened or closed - doesn't matter

 

[exceltm]

You can use PowerQuery
Merge table from workbook2 to table from workbook1 by employee then expand Table column in result QueryTable with status only, then load to sheet

workbook1 can be opened or closed - doesn't matter

I'm using MS Excel 2013 from MS Office Professional Plus 2013. There are about 8000 rows in my spreadsheet table.
Could you please dumb this down for me, as I tried your suggestion first but encountered some roadblocks. There are a few missing steps. For example, I had to first download the Power Query add-on, which I now have loaded.

I clicked on Power Query | Merge, but when it opened the merge box, it said, "No preview is available. There are currently no queries that can be merged."

Merge by employee?

 

[sandy666]

workbook1 - change range to Excel Table (Ctrl+T)
workbook2 - change range to Excel Table (Ctrl+T)

from workbook2 Data - New Query - From file (table from workbook1)
select your table -edit - close&load - as connection
select any cell on table workbook2 - Data - From Table

now you've two tables loaded into PowerQuery Editor and you can use Merge Queries from PQ menu

Excel Power Query (Get & Transform) Tutorial for Beginners

 

[sandy666]

or see video here: merge 2 tables from different files