Excel Data Pull

[RTAssocia]

Apologies if posted elsewhere, however, I have been unable to successfully locate how to perform the below action.
I have Two Workbooks, WB1 & WB2, both have no more than 4000 line items in each workbook.
In WB1 I have entries within Column G that I need to bring over into WB2.

I am trying only to pull from WB1 into WB2, those which have exact matching values from Column B in WB2 & Column A in WB1.
AAm explaining this question right? I'm at wits end trying to figure this out, please help!

 

[kevin9999]

Welcome to the forum!
Just a few questions..
Which workbook will the code be sitting in? Will both workbooks already be open? What are the names of the 2 workbooks? Is row 1 in each workbook a header row & does the data start in row 2? Is it just constants that are being copied, or is it the values returned from formulas & do you only want the values copied? Do you want the values copied to the first available (empty) row in WB2 - and to which column? Is it just column G values you want copied (not the entire row)?
It would really help if you could provide copies of your 2 workbooks using the XL2BB add in.

 

[RTAssocia]

HI :D & Thank you!!

The code would sit in WB 2, within Column G.
Both Workbooks would be open, with 1st Row as Header & Data start on 2nd row.
Trying to have the data within WB1 G Column populate within WB2 Column G- only if matching values are found within WB2 Column A, against WB1 Column A.


WB1:

WB1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	A_ID	O_F_N	H_P	W_P	M_P	O_P	E_A	E_C	E_R	E_P	E_P2	E_P3	O_T	U_A
2	1234567890	K			123-456-7890		k@mail.com	k	f	123-456-7890
Sheet1

v

WB2:

WB2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A_ID1	A_ID2	T_D	T_A	T_R	T_C	Column1	A_N	C_D	A_N2	C_017	C_018	A_R_N
2	1234	1234567890	1/1/2023 00:00	0	g	g		a	gma	rtc	1/1/2023 00:00	12/1/2023 00:00	rtc
Sheet1

 

[kevin9999]

Thank you for that. They look like tables, not ranges - are they?

 

[RTAssocia]

Yes, as Tables.

 

[alansidman]

With Power Query, bring each table into the PQ Editor. Join the two table in a Merge on the common field. (Outer Join). In the new column created extract only the field required. Close and Load the combined data to Excel.

Power Query is an integral part of O365 and is called Get and Transform Data and found on the Data Tab of the Ribbon.

 

[kevin9999]

Is it simply the case that you want to return the email addresses from column G of WB1 to column G of WB2 - if it finds a match column B in WB2 in column A in WB1? If that is the case, then try the following code on a copy of your workbooks. Please make sure that the table names are changed to whatever your actual table names are, and that both workbooks are open at the time you run the code. This code must sit in a standard module in WB2.

Sub WB1_WB2()
    With ThisWorkbook.Worksheets("Sheet1").Range("G2")
        .Formula = "=XLOOKUP([@[A_ID2]],WB1.xlsx!Table1[A_ID],WB1.xlsx!Table1[E_A],"""")"
        .Value = .Value
    End With
End Sub

 

[kevin9999]

To check multiple values:

Sub WB1_WB2()
    With ThisWorkbook.Worksheets("Sheet1").Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row)
        .Formula = "=XLOOKUP([@[A_ID2]],WB1.xlsx!Table1[A_ID],WB1.xlsx!Table1[E_A],"""")"
        .Value = .Value
    End With
End Sub

 

[RTAssocia]

Most obliged!!
Trying them out :D