Excel 2013 - VB for Nested IF Statement

Hello -

Hoping someone can assist in developing a code that will do the following once ran (see attached sample file):

If number in column E (new file) does not match the number in column E (master) then:
1.  add row to bottom of Main tab
2.  show a ‘N’ in column Q and
3.  show ‘NEW’ in column R
4.  go back to New File tab and check next row

If number in column E (new file) matches number in column E (master), and there is a ‘Y’ in column Q, then:
1.  add to bottom of Main tab
2.  show ‘Y’ in column Q
3.  show ‘DUPLICATE - DISCLOSURE in column R
4.  go back to New File tab and check next row

If number in column E (new file) matches number in column E (master), and there is a ‘N’ or Blank in column Q, then:
1.  add to bottom of Main tab
2.  show ‘N’ in column Q
3.  show ‘DUPLICATE – NON-DISCLOSURE’ in column R
4.  go back to New File tab and check next row

Any assistance would be greatly appreciated!!
EE-Example.xlsx
LVL 1
EscanabaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SANTABABYCommented:
Attached file has code in Module1. Please try the macro.

Option Base 1


Sub ProcessRecords()
Dim wksmain As Worksheet, wksnew As Worksheet
Dim lastrow As Integer, newrow As Integer
Dim c As Range



Set wksmain = ActiveWorkbook.Sheets("Main")
Set wksnew = ActiveWorkbook.Sheets("New File")

lastrow = wksmain.Range("A" & wksmain.Rows.Count).End(xlUp).Row
newrow = wksnew.Range("A" & wksnew.Rows.Count).End(xlUp).Row

For r = 2 To newrow
    Set c = wksmain.Columns("E:E").Find(What:=wksnew.Range("E" & r), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If c Is Nothing Then
     'If number in column E (new file) does not match the number in column E (master) then:
    '1.  add row to bottom of Main tab
    '2.  show a ‘N’ in column Q and
    '3.  show ‘NEW’ in column R
    '4.  go back to New File tab and check next row
       lastrow = lastrow + 1
        wksnew.Rows(r).Copy Destination:=wksmain.Cells(lastrow, 1)
        wksmain.Range("Q" & lastrow) = "N"
        wksmain.Range("R" & lastrow) = "NEW"
    Else
        rfnd = c.Row
        If wksmain.Range("Q" & rfnd) = "Y" Then
        'If number in column E (new file) matches number in column E (master), and there is a ‘Y’ in column Q, then:
        '1.  add to bottom of Main tab
        '2.  show ‘Y’ in column Q
        '3.  show ‘DUPLICATE - DISCLOSURE in column R
        '4.  go back to New File tab and check next row
            lastrow = lastrow + 1
            wksnew.Rows(r).Copy Destination:=wksmain.Cells(lastrow, 1)
            wksmain.Range("Q" & lastrow) = "Y"
            wksmain.Range("R" & lastrow) = "DUPLICATE - DISCLOSURE"
        ElseIf wksmain.Range("Q" & rfnd) = "N" Or Trim(wksmain.Range("Q" & rfnd)) = "" Then
        'If number in column E (new file) matches number in column E (master), and there is a ‘N’ or Blank in column Q, then:
        '1.  add to bottom of Main tab
        '2.  show ‘N’ in column Q
        '3.  show ‘DUPLICATE – NON-DISCLOSURE’ in column R
        '4.  go back to New File tab and check next row
            lastrow = lastrow + 1
            wksnew.Rows(r).Copy Destination:=wksmain.Cells(lastrow, 1)
            wksmain.Range("Q" & lastrow) = "N"
            wksmain.Range("R" & lastrow) = "DUPLICATE - DISCLOSURE"
        End If
    End If

Next r

End Sub
EE-Example-SB.xlsm
0
EscanabaAuthor Commented:
Thank you!  Its close but not there yet.  I might not fully explained or at least not well enough.  
The result for George Washington and George Bush are not correct.  For GW it identifies it as a duplicate which is correct but the code needs to look at the status (column Q) of that first record (row 4).  If it's an N or blank the response in column R should be Duplicate - Non-Disclosure.  If its a duplicate and the status on the original record is Y then the response is Duplicate - Disclosure  The GB result is incorrect because the original record (row 6) shows the status as blank which should be a Duplicate - Non-Disclosure.

Basically want to add a new monthly file to the 'New File' tab, run the macro and have it identify if the accounts listed have been reviewed already and if so what the status of the original record is set at.
0
SANTABABYCommented:
Sorry there was a typo (Copy Paste issue):
Here is the modified code:
Option Base 1


Sub ProcessRecords()
Dim wksmain As Worksheet, wksnew As Worksheet
Dim lastrow As Integer, newrow As Integer
Dim c As Range



Set wksmain = ActiveWorkbook.Sheets("Main")
Set wksnew = ActiveWorkbook.Sheets("New File")

lastrow = wksmain.Range("A" & wksmain.Rows.Count).End(xlUp).Row
newrow = wksnew.Range("A" & wksnew.Rows.Count).End(xlUp).Row

For r = 2 To newrow
    Set c = wksmain.Columns("E:E").Find(What:=wksnew.Range("E" & r), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If c Is Nothing Then
     'If number in column E (new file) does not match the number in column E (master) then:
    '1.  add row to bottom of Main tab
    '2.  show a ‘N’ in column Q and
    '3.  show ‘NEW’ in column R
    '4.  go back to New File tab and check next row
       lastrow = lastrow + 1
        wksnew.Rows(r).Copy Destination:=wksmain.Cells(lastrow, 1)
        wksmain.Range("Q" & lastrow) = "N"
        wksmain.Range("R" & lastrow) = "NEW"
    Else
        rfnd = c.Row
        If wksmain.Range("Q" & rfnd) = "Y" Then
        'If number in column E (new file) matches number in column E (master), and there is a ‘Y’ in column Q, then:
        '1.  add to bottom of Main tab
        '2.  show ‘Y’ in column Q
        '3.  show ‘DUPLICATE - DISCLOSURE in column R
        '4.  go back to New File tab and check next row
            lastrow = lastrow + 1
            wksnew.Rows(r).Copy Destination:=wksmain.Cells(lastrow, 1)
            wksmain.Range("Q" & lastrow) = "Y"
            wksmain.Range("R" & lastrow) = "DUPLICATE - DISCLOSURE"
        ElseIf wksmain.Range("Q" & rfnd) = "N" Or Trim(wksmain.Range("Q" & rfnd)) = "" Then
        'If number in column E (new file) matches number in column E (master), and there is a ‘N’ or Blank in column Q, then:
        '1.  add to bottom of Main tab
        '2.  show ‘N’ in column Q
        '3.  show ‘DUPLICATE – NON-DISCLOSURE’ in column R
        '4.  go back to New File tab and check next row
            lastrow = lastrow + 1
            wksnew.Rows(r).Copy Destination:=wksmain.Cells(lastrow, 1)
            wksmain.Range("Q" & lastrow) = "N"
            wksmain.Range("R" & lastrow) = "DUPLICATE – NON-DISCLOSURE"
        End If
    End If

Next r

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EscanabaAuthor Commented:
Perfect!  Thank you so much for putting this together!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.