Listing Unique Data for 2 Lists

[DataEntrySGR]

I am comparing a membership rosters for two different years in excel. I need to find records from 2016 that are not listed on the 2017 listing or are not listing with the same chapter from 2016 (Criteria: ID from 2016 must be on list from 2017 AND ID from 2016 cannot have a different chapter on the 2017 listing). Each record has a unique ID that corresponds to that person and each person would also need to be in the same chapter (ex: ID 1234 has alpha in the chapter column). Right now I am taking each chapter listing from both years, (color coded one year blue and left the other black) and placing it into another sheet. From there I am using the =countif function to show me which record is unique. I have to repeat this for each individual chapter. I am looking for a way to do this for the whole document. Let me know if I need to be a little more clear.

 

[Fluff]

Hi & welcome to MrExcel.
a few questions
1) are both rosters in the same workbook? if not what are the workbook names?
2) What are the sheets names of the 2016 & 2017 rosters
3) What columns are the ID & chapter?
4) Where do you want to output the non matching records?

 

[DataEntrySGR]

1) are both rosters in the same workbook? if not what are the workbook names?
Yes they are

2) What are the sheets names of the 2016 & 2017 rosters
the 2016 is "2016-2017" and the 2017 is "2017-2018"

3) What columns are the ID & chapter?
ID column is D for both and the chapter column is B for both

4) Where do you want to output the non matching records?
I would like it in another sheet if possible.

 

[Fluff]

Ok, try this

Sub Comparedata()

   Dim Cl As Range
   Dim ValU As String
   Dim Itm As Variant
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("2016-2017")
   Set Ws2 = Sheets("2017-2018")
Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("D2", Ws1.Range("D" & Rows.count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, -2).Value
         If Not .exists(ValU) Then .Add ValU, Cl.Offset(, -3)
      Next Cl
      For Each Cl In Ws2.Range("D2", Ws2.Range("D" & Rows.count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, -2).Value
         If .exists(ValU) Then .Remove ValU
      Next Cl
      Sheets.Add.Name = "Missing"
      For Each Itm In .items
         Itm.EntireRow.Copy Sheets("Missing").Range("A" & Rows.count).End(xlUp).Offset(1)
      Next Itm
   End With
End Sub

 

[DataEntrySGR]

Works perfectly! Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback