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

exceltm

New Member
Joined
Apr 5, 2010
Messages
32
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
employeeCITYStateSALESstatus
8DALLASTX$30,8351
12SHREVEPORTLA$71,638
13METAIRIELA$245,0133
19HARTFORDCT$181,049
20BATON ROUGELA$2,166,3851
23OMAHANE$24,132 m
29WATERBURYCT$937,589
33JACKSONVILLEFL$21,9801
35OMAHANE$3,066,541
49WATERBURYCT$240,774m
workbook 2
employeeCITYState
SALESstatus
8
DALLASTX$42,184
12SHREVEPORTLA$47,845
19
HartfordCT$252,426
20BATON ROUGELA$3,332,213
23OMAHANE$34,241
29WATERBURYCT$1,402,102
33JACKSONVILLEFL$28,294
35OMAHANE$4,839,064
49WATERBURYCT$487,642
55New HavenCT$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>
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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.
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,068
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

New Member
Joined
Apr 5, 2010
Messages
32
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,068
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,596
Members
410,624
Latest member
smartsanjiv64
Top