I have posted this in another forum and got no reply so I am trying here

http://www.mrexcel.com/forum/excel-q...-criteria.html





All,

The code below is part of a bigger project and this is part of a larger code set.

1. I have a “From sheet” and a “To sheet”
2. No row is pulled over if column “B” in the “From sheet” if it has another number besides zero
3. I need only pull over information from the “From sheet” only if in the “To sheet” the end date in column “E” is blank same number in column “A” must match in both “From sheet” and “To sheet” must match
4. I need to pull over new rows if they are not in the “To sheet” that on has a zero in column “B” from the “From sheet”.
5. All update and append must be paste value only




“From sheet”

A B C D E
case parent description start end
111 0 doug 1/5/2016 1/6/2016
222 0 why 1/5/2016 1/6/2016
333 0 me 1/5/2016 1/6/2016
444 0 it 1/5/2016 1/6/2016
555 0 why 1/5/2016
666 0 me 1/5/2016 1/6/2016
777 0 it 1/5/2016 1/6/2016
888 0 why 1/5/2016 1/6/2016
999 1 me 1/5/2016
1110 0 it 1/5/2016 1/6/2016
1221 1 why 1/5/2016 1/6/2016
1332 0 me 1/5/2016 1/6/2016
1443 0 it 1/5/2016 1/6/2016
1554 0 why 1/5/2016
1665 1 me 1/5/2016
1776 1 it 1/5/2016
1887 0 why 1/5/2016
1998 0 me 1/5/2016
2109 0 it 1/5/2016
2220 1 why 1/5/2016 1/6/2016
2331 0 me 1/5/2016 1/6/2016
2442 0 why 1/5/2016 1/6/2016

“To sheet”

A B C D E
case parent description start end
111 0 1/5/2016
333 0 1/5/2016
444 0 1/5/2016
555 0 1/5/2016
666 0 1/5/2016
777 0 1/5/2016
888 0 why 1/5/2016 1/6/2016
1110 0 it 1/5/2016 1/6/2016
1332 0 me 1/5/2016 1/6/2016








Sub comparerows()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Fcount As Long
Dim Fcount_S As Long
Dim Tcount As Long
Dim Tcount_S As Long
Dim Ftemp As String
Dim Ttemp As String
Dim FParent As String
Dim Tdate As String

Set ws1 = ActiveWorkbook.Sheets("From")
Set ws2 = ActiveWorkbook.Sheets("To")

Fcount = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
Tcount = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

If Tcount > 1 Then
For Fcount_S = 2 To Fcount
Ftemp = ws1.Cells(Fcount_S, 1).Value
FParent = ws1.Cells(Fcount_S, 2).Value
If FParent = 0 Then

For Tcount_S = 2 To Tcount
Ttemp = ws2.Cells(Tcount_S, 1).Value
Tdate = ws2.Cells(Tcount_S, 5).Value 'End date
If Ftemp = Ttemp Then
ws1.Cells(Fcount_S, 6).Value = 1

If Tdate = "" Then
ws2.Cells(Tcount_S, 3).Value = ws1.Cells(Fcount_S, 3).Value
ws2.Cells(Tcount_S, 5).Value = ws1.Cells(Fcount_S, 5).Value
Tcount_S = 2

Exit For
End If
Else


End If


Next Tcount_S

Else
ws1.Cells(Fcount_S, 6).Value = 1
End If
Next Fcount_S
Else
Tcount_S = 2
For Fcount_S = 2 To Fcount
FParent = ws1.Cells(Fcount_S, 2).Value
If FParent = 0 Then
ws2.Cells(Tcount_S, 1).Value = ws1.Cells(Fcount_S, 1).Value
ws2.Cells(Tcount_S, 2).Value = ws1.Cells(Fcount_S, 2).Value
ws2.Cells(Tcount_S, 3).Value = ws1.Cells(Fcount_S, 3).Value
ws2.Cells(Tcount_S, 4).Value = ws1.Cells(Fcount_S, 4).Value
ws2.Cells(Tcount_S, 5).Value = ws1.Cells(Fcount_S, 5).Value
Tcount_S = Tcount_S + 1

End If

Next Fcount_S

End If

Tcount = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
Tcount = Tcount + 1

For Fcount_S = 2 To Fcount
If ws1.Cells(Fcount_S, 6).Value = "" Then
ws2.Cells(Tcount_S, 1).Value = ws1.Cells(Fcount_S, 1).Value
ws2.Cells(Tcount_S, 2).Value = ws1.Cells(Fcount_S, 2).Value
ws2.Cells(Tcount_S, 3).Value = ws1.Cells(Fcount_S, 3).Value
ws2.Cells(Tcount_S, 4).Value = ws1.Cells(Fcount_S, 4).Value
ws2.Cells(Tcount_S, 5).Value = ws1.Cells(Fcount_S, 5).Value
Tcount_S = Tcount_S + 1
End If

Next Fcount_S
'ws2.Activate
ws2.Range("A1").CurrentRegion.Sort key1:=ws2.Range("A1"), order1:=xlAscending, Header:=xlYes

ws1.Columns(6).EntireColumn.Delete
End Sub