How to compare 2 different excel sheets and output results on 3rd sheet

[sunwave]

I want to compare the email addresses on sheet1 to sheet 2 and output the results on sheet 3.

Sheet 1 and Sheet 2 have most of the same email addresses with a few extras (different addresses) on each.
There are extra email addresses on sheet 1 but are not on sheet 2.
There are extra email addresses on sheet 2 but are not on sheet 1. (Vice versa)

The results:
I want the results to output the email addresses from sheet 1, that are missing from sheet 2, labeled as "Not in Sheet 2"
I want the results to output the email addresses from sheet 2, that are missing from sheet 1, labeled as "Not in sheet 1" (Vice Versa)
The results from both sheets should output on Sheet 3.


Images of Sheet 1, Sheet 2, Sheet 3, are provided below.

How would I code this? Thank you!!

 

[sunwave]

Using VBA ofcourse!!!

 

[kevin9999]

There's a number of ways you could do this, here's just one. Please try on a copy of your workbook. Assumes your layout is exactly as you images suggest, and that the code names of the sheets match the sheet tab names.

Sub Copy_Emails()
    Application.ScreenUpdating = False
    With Sheet1
        If .AutoFilterMode Then .AutoFilter.ShowAllData
        With .Range("E2:E" & .Cells(Rows.Count, "D").End(xlUp).Row)
            .Value = Evaluate("IF(ISNA(MATCH(" & .Offset(, -1).Address(, , , 1) & ",Sheet2!B:B,0)),""Not in Sheet 2"","""")")
        End With
        With .Range("A1").CurrentRegion
            .AutoFilter 5, "<>"
            If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
                .Offset(1, 3).Resize(.Rows.Count - 1, 2).Copy Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .AutoFilter
            End If
        End With
        .Columns("E").ClearContents
    End With
    
    With Sheet2
        If .AutoFilterMode Then .AutoFilter.ShowAllData
        With .Range("E2:E" & .Cells(Rows.Count, "B").End(xlUp).Row)
            .Value = Evaluate("IF(ISNA(MATCH(" & .Offset(, -3).Address(, , , 1) & ",Sheet1!D:D,0)),""Not in Sheet 1"","""")")
        End With
        With .Range("A1").CurrentRegion
            .AutoFilter 5, "<>"
            If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
                .Columns("C:D").Hidden = True
                .Offset(1, 1).Resize(.Rows.Count - 1, 4).Copy Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .Columns("C:D").Hidden = False
                .AutoFilter
            End If
        End With
        .Columns("E").ClearContents
    End With
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.excelforum.com/excel-pr...nd-output-results-on-3rd-sheet-using-vba.html
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.