Selected Answer
Hello excel1,
>> updated Aug. 19/20 <<
I wasn't able to view the file using the link you provided.
The following will check to see if the conditions are met then check to see if the name on sheet1 is on sheet2 and if not copy it to the next blank line on sheet2.
Sub Find_And_Copy()
' macro written by WillieD24 Ayg. 19/20
' variables - some possibilities
Dim WsSht1, WsSht2 As Worksheet
Dim Lrow1, Lrow2 As Integer
Dim Rx1 As Integer, Rx2 As Integer
Dim FindWhat, Found As String
Set WsSht1 = Worksheets("Sheet1")
Set WsSht2 = Worksheets("Sheet2")
' Find number of rows with data in Column B on WsSht1 and WsSht2
Lrow1 = WsSht1.Cells(Rows.Count, 2).End(xlUp).Row
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
Rx1 = 2 ' row number starting value on WsSht1; assuming row 1 is a header row
Rx2 = 2 ' row number starting value on WsSht2; assuming row 1 is a header row
' Condition 1: (H Rx1) > (D Rx1) and (H Rx1) > (K Rx1)
Line101:
If Rx1 > Lrow1 Then End ' all rows on WsSht1 have been checked
If WsSht1.Cells(Rx1, 11) > WsSht1.Cells(Rx1, 4) And WsSht1.Cells(Rx1, 8) > WsSht1.Cells(Rx1, 11) Then GoTo Line102 Else GoTo Line103
Line102: ' condition 1 met - check for WsSht1(B) in WsSht2(B)
FindWhat = WsSht1.Cells(Rx1, 2)
Found = WsSht2.Cells(Rx2, 2)
If FindWhat = Found Then GoTo Line103 ' name found - check next WsSht1(B)
Rx2 = Rx2 + 1
If Rx2 > Lrow2 Then GoTo Line104 ' name not found
GoTo Line101
Line103: ' condition 1 not met or name exists on WsSht2 - check next row on WsSht1
Rx1 = Rx1 + 1
Rx2 = 2
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
If Rx1 > Lrow1 Then GoTo Line200 ' all rows on WsSht1 have been checked for condition 1
GoTo Line101
Line104: ' name not found on WsSht2
WsSht2.Range("B" & Rx2) = WsSht1.Range("B" & Rx1) ' enter nam from WsSht1(B) on WsSht2(B)
Rx1 = Rx1 + 1
Rx2 = 2
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
GoTo Line101 ' look for next WsSht(B) in WsSht2(B)
Line200:
' Condition 2: (H Rx1) < (D Rx1) and (H Rx1) < (K Rx1)
Rx1 = 2
Rx2 = 2
Line201:
If Rx1 > Lrow1 Then End ' all rows on WsSht1 have been checked
If WsSht1.Cells(Rx1, 11) < WsSht1.Cells(Rx1, 4) And WsSht1.Cells(Rx1, 8) < WsSht1.Cells(Rx1, 11) Then GoTo Line202 Else GoTo Line203
Line202: ' condition 2 met - check for WsSht1(B) in WsSht2(B)
FindWhat = WsSht1.Cells(Rx1, 2)
Found = WsSht2.Cells(Rx2, 2)
If FindWhat = Found Then GoTo Line203 ' name found - check next WsSht1(B)
Rx2 = Rx2 + 1
If Rx2 > Lrow2 Then GoTo Line204 ' name not found
GoTo Line201
Line203: ' condition 2 not met or name exists on WsSht2 - check next row on WsSht1
Rx1 = Rx1 + 1
Rx2 = 2
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
If Rx1 > Lrow1 Then End 'GoTo line999
GoTo Line201
Line204: ' name not found on WsSht2
WsSht2.Range("B" & Rx2) = WsSht1.Range("B" & Rx1) ' enter nam from WsSht1(B) on WsSht2(B)
Rx1 = Rx1 + 1
Rx2 = 2
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
GoTo Line201 ' look for next WsSht(B) in WsSht2(B)
End Sub
See the attached file to try it out.
Hope this helps
Cheers ;-}
Hope this helps to get you started.