Find changes in one sheet and load in another

[jcmckeon]

I have a training dashboard with employee names and training they completed. I get new employee lists, I add them to sheet 2. I want to compare sheet 2 (the new employees) with sheet 1 and add a new row with the new employee so I don't add names and have the training columns not match the employee name.

Is there a way to check name on sheet 1, if there then ignore the new entries on sheet 2. If NOT THERE add to sheet 1 by creating new row and adding.

See attached. HOPEFULLY I DID THE MINISHEET CORRECTLY

move employee.xlsx
	C
22
Sheet 2

move employee.xlsx
	A	B	C	D	E	F	G	H	I
2	Last Name	First Name	Dept	Compliance	AS9100	Certificates	Shop	On the Job	Epicor
3	Balakier	Maciej	LATHE	x	x	x
4	Bommer	John	ASSEMBLY	x	x	x	x	x	x
5	Borowski	Adam	LATHE	x	x	x	x	x	x
6	Bourguillon	Tom	MFG	x	x	x	x	x	x
7	Brackett	Marie	MANGMENT	x	x	x	x	x	x
8	Brown	NewGuy
9	Calzone	Jeffrey	MILLING	x	x	x	x	x	x
10	Calzone	Joseph	MILLING	x	x	x	x	x	x
11	Canepari	Vincent	MILLING	x	x	x	x	x	x
Sheet 1

 

[Saurabhj]

Hi, One query -

Is the comparison based on First name + Last Name + Dept or only First Name + Last Name ?

Thanks,
Saurabh

 

[jcmckeon]

Hi, One query -

Is the comparison based on First name + Last Name + Dept or only First Name + Last Name ?

Thanks,
Saurabh

Hello Saurabhj, on first and last name

 

[Saurabhj]

Hi, Please check below code.

Code is as per below sheet2 format. If there is any change please share the format of Sheet2.

Training.xlsm
	A	B	C
1	Last Name	First Name
2	Balakier	Paul
3	Bommer	John
4	Borowski	Adam
5	Brown	Jim
6
7
8
9
10
11
Sheet2

Option Explicit

Sub addTeam()
Dim foundcell As Range, rowno As Integer, isAvailable As String
Dim targetrow As Integer, sourcerow As Integer, rowAvailable As Integer

sourcerow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

For rowno = 2 To sourcerow
    targetrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    isAvailable = "N"
    For rowAvailable = 2 To targetrow
        If Sheets("Sheet2").Range("A" & rowno) = Sheets("Sheet1").Range("A" & rowno) And Sheets("Sheet2").Range("B" & rowno) = Sheets("Sheet1").Range("B" & rowno) Then
            isAvailable = "Y"
            Exit For
        End If
    Next
    If isAvailable = "N" Then
        Sheets("Sheet1").Range("A" & targetrow + 1) = Sheets("Sheet2").Range("A" & rowno)
        Sheets("Sheet1").Range("B" & targetrow + 1) = Sheets("Sheet2").Range("B" & rowno)
    End If
Next

End Sub

 

[jcmckeon]

Option Explicit Sub addTeam() Dim foundcell As Range, rowno As Integer, isAvailable As String Dim targetrow As Integer, sourcerow As Integer, rowAvailable As Integer sourcerow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row For rowno = 2 To sourcerow targetrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row isAvailable = "N" For rowAvailable = 2 To targetrow If Sheets("Sheet2").Range("A" & rowno) = Sheets("Sheet1").Range("A" & rowno) And Sheets("Sheet2").Range("B" & rowno) = Sheets("Sheet1").Range("B" & rowno) Then isAvailable = "Y" Exit For End If Next If isAvailable = "N" Then Sheets("Sheet1").Range("A" & targetrow + 1) = Sheets("Sheet2").Range("A" & rowno) Sheets("Sheet1").Range("B" & targetrow + 1) = Sheets("Sheet2").Range("B" & rowno) End If Next End Sub

Hello Saurabhi, thank you for this. I'm having trouble. I opened the Excel workbook, went to developer tab, visual basic, copied/pasted code you provided and got this error. What did I do wrong?

 

[Fluff]

Delete the very first line in that module.
Also in future when copying code, it's best to use the copy icon at the top right of the code window. That will prevent errors like that from happening again.

 

[jcmckeon]

Delete the very first line in that module.
Also in future when copying code, it's best to use the copy icon at the top right of the code window. That will prevent errors like that from happening again.
View attachment 57854

Thank you for this, I'll give it a try. How about a simpler solution. Use conditional format to compare new to main. If new has a name NOT shown on main, highlight that name.

 

[Saurabhj]

Hi,

Add module in VBA editor as below screenshot and copy the code in module.

 

[jcmckeon]

Hi,

Add module in VBA editor as below screenshot and copy the code in module.

View attachment 57949

Thank you. Be well

 

[Saurabhj]

Thanks for the feedback.