Merge values from old sheet with new sheet and flag new entries

[covareo]

I have a task that is asked of my team every year that is very time-consuming. We get a listing of all employees that is missing key information that we manually update each year. I am hoping that you can help us to automate this process. We have a new list as the "Working" tab in an Excel file and last year's list as the "Old" tab in the same file (attached). I am hoping to have a formula or script that can search each Employee_Num from the "Working" tab for information on the "Old" tab, and if it's found, populate columns C, D, and E with any data found. Additionally, if the record is not found in "Old", mark column A on the "Working" sheet with an X so we can examine and confirm its a new hire. I have mocked this up in the "Result" tab.

The lookup value is the Employee_Num field. The first column is an automatically generated iterative counter and not important. If values exist in the "Working" tab they should be respected and kept as they are. Empty values are common and inconsistent.

Here is the "Working" Tab:

Employee-Update.xlsx
	A	B	C	D	E	F	G	H	I	J
1		New	Hire_Date	Type	Term_Date	Employee_Num	LastName	FirstName	Initial	Depend
2	1					001234567	La Rosa	Le Shawn	L	0
3	2					003456789	Wilson	Barbara	W	4
4	3					055527240	D'Angelo	Garett	S	2
5	4					011846457	McDough	Betty		0
6	5					016234437	Rinni	Joshua	M	0
7	6		2/1/21	PT	7/14/23	001423289	Olson	Enrique		2
8	7					001293213	Erickson	Kyle		0
Working

Here is the "Old" tab:

Employee-Update.xlsx
	A	B	C	D	E	F	G	H	I
1		Hire_Date	Type	Term_Date	Employee_Num	LastName	FirstName	Initial	Depend
2	1	6/14/12	FT		001234567	La Rosa	Le Shawn	L	0
3	2	2/24/92	FT		003456789	Wilson	Barbara	W	4
4	3	9/15/23	FT		055527240	D'Angelo	Garett	S	2
5	4		FT		011846457	McDough	Betty		0
6	5	3/13/21	FT		016234437	Rinni	Joshua	M	0
7	6	2/1/21	PT	7/14/23	001423289	Olson	Enrique		2
Old

Here is a mocked-up "Result" after the run:

Employee-Update.xlsx
	A	B	C	D	E	F	G	H	I	J
1		New	Hire_Date	Type	Term_Date	Employee_Num	LastName	FirstName	Initial	Depend
2	1		6/14/12	FT		001234567	La Rosa	Le Shawn	L	0
3	2		2/24/92	FT		003456789	Wilson	Barbara	W	4
4	3		9/15/23	FT		055527240	D'Angelo	Garett	S	2
5	4			FT		011846457	McDough	Betty		0
6	5		3/13/21	FT		016234437	Rinni	Joshua	M	0
7	6		2/1/21	PT	7/14/23	001423289	Olson	Enrique		2
8	7	X				001293213	Erickson	Kyle		0
Result

 

[skillilea]

I thought the requirement was to put an X in column B if they are are new employee.

If you don't want the X in column B you can remove this section of the code:

This will copy hire_date, type, term_data from Old to New

Public Sub EmployeeTransfer()
    
    Dim wWs As Worksheet, oWs As Worksheet
    Dim ar As Variant
    Dim rng As Range
    Dim i As Long, x As Long
    Dim empNum As Variant
    
    Set wWs = ThisWorkbook.Sheets("Working")
    Set oWs = ThisWorkbook.Sheets("Old")

    ar = oWs.Range("A1").CurrentRegion
    Set rng = wWs.Range("A1").CurrentRegion
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For i = 2 To rng.Rows.Count   ' leave the header out
       empNum = rng(i, 6).Value
    
        ' loop to find out if in OLD
        For x = 1 To UBound(ar)
            If ar(x, 5) = empNum Then
                wWs.Cells(i, 3).Value = ar(x, 2)
                wWs.Cells(i, 4).Value = ar(x, 3)
                wWs.Cells(i, 5).Value = ar(x, 4)
            End If
        Next
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
     
    Set oWs = Nothing
    Set wWs = Nothing
End Sub

 

[covareo]

I tried doing this with VLOOKUP, but it was always blank/FALSE like this VBA has returned - is it because they are dates being copied? I tried:

=IFERROR(VLOOKUP(E2, 'Old'!B:E, 2, FALSE), "")

Actually using this formula seems to work when i re-order columns so Employee_Num is before the other columns:

=IFERROR(VLOOKUP(B2, 'Old'!B:E, 1, FALSE), "")

Question: How do I have the cells that DON'T have results stay BLANK/NULL as opposed to 1/0/00 for Dates and 0 for Type?

 

[covareo]

I thought the requirement was to put an X in column B if they are are new employee.

If you don't want the X in column B you can remove this section of the code:

This will copy hire_date, type, term_data from Old to New

Public Sub EmployeeTransfer()
   
    Dim wWs As Worksheet, oWs As Worksheet
    Dim ar As Variant
    Dim rng As Range
    Dim i As Long, x As Long
    Dim empNum As Variant
   
    Set wWs = ThisWorkbook.Sheets("Working")
    Set oWs = ThisWorkbook.Sheets("Old")

    ar = oWs.Range("A1").CurrentRegion
    Set rng = wWs.Range("A1").CurrentRegion
   
    Application.EnableEvents = False
    Application.ScreenUpdating = False
   
    For i = 2 To rng.Rows.Count   ' leave the header out
       empNum = rng(i, 6).Value
   
        ' loop to find out if in OLD
        For x = 1 To UBound(ar)
            If ar(x, 5) = empNum Then
                wWs.Cells(i, 3).Value = ar(x, 2)
                wWs.Cells(i, 4).Value = ar(x, 3)
                wWs.Cells(i, 5).Value = ar(x, 4)
            End If
        Next
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    Set oWs = Nothing
    Set wWs = Nothing
End Sub

This does not seem to return anything - here is the sheet after running:

Employee-Update.xlsx
	A	B	C	D	E	F	G	H	I	J
1		New	Hire_Date	Type	Term_Date	Employee_Num	LastName	FirstName	Initial	Depend
2	1					001234567	La Rosa	Le Shawn	L	0
3	2					003456789	Wilson	Barbara	W	4
4	3					055527240	D'Angelo	Garett	S	2
5	4					011846457	McDough	Betty		0
6	5					016234437	Rinni	Joshua	M	0
7	6		2/1/21	PT	7/14/23	001423289	Olson	Enrique		2
8	7					001293213	Erickson	Kyle		0
Working

I think I'm super close w VLOOKUP but just need to preserve the blanks instead of replacing blank dates with "1/0/00" and blank Types with "0". To get this result I had to re-order my columns to have what was F (Employee_Num) move to Column B before the other values to be copied (I did this in a separate sheet so as not to affect the code you provided). You can see the results of my VLOOKUP here:

Employee-Update-Vlookup.xlsx
	A	B	C	D	E	F	G	H	I	J
1		New	Employee_Num	Hire_Date	Type	Term_Date	LastName	FirstName	Initial	Depend
2	1		001234567	6/14/12	FT	1/0/00	La Rosa	Le Shawn	L	0
3	2		003456789	2/24/92	FT	1/0/00	Wilson	Barbara	W	4
4	3		055527240	9/15/23	FT	1/0/00	D'Angelo	Garett	S	2
5	4		011846457	1/0/00	0	1/0/00	McDough	Betty		0
6	5		016234437	3/13/21	FT	1/0/00	Rinni	Joshua	M	0
7	6		001423289	2/1/21	PT	7/14/23	Olson	Enrique		2
8	7		001293213				Erickson	Kyle		0
Working

Cell Formulas
Range	Formula
D2:D8	D2	=IFERROR(VLOOKUP(C2, Old!B:E, 2, FALSE), "")
E2:E8	E2	=IFERROR(VLOOKUP(C2, Old!B:E, 3, FALSE), "")
F2:F8	F2	=IFERROR(VLOOKUP(C2, Old!B:E, 4, FALSE), "")

=IFERROR(VLOOKUP(B2, 'Old'!B:E, 1, FALSE), "")

Any advice on how to keep the NULL/Blank values in this method?

 

[covareo]

For anyone who sees this and is struggling with the same issue here is the solution eventually came to:

1. VLOOKUP only searches the first column of data for matches, so I had to re-order columns to have the search field first
2. I had to specify date formatting or the VLOOKUP just brought the unformatted time integer
3. I added some additional functions to preserve blanks as the default would add 0's to blanks.

I ended up using the following:

=IF(ISBLANK(VLOOKUP(B2, 'Old'!B:E, 2, FALSE)), "", IFERROR(VLOOKUP(B2, 'Old'!B:E, 2, FALSE), ""))