Question

Problem: 1. There are two lists of customers: those who bought products in 2016 and those who bought products in 2017. Each of these lists is in alphabetical order (by last name of customer). To simplify the application, we'll assume that no two last names in the database are identical. For example, if we see Smith on the 2016 list and the 2017 list, you can assume that it is the same Smith in both cases. We want to generate three new lists: those who bought only in 2016, those who bought only in 2017, and those who bought in both years. Please assign the 'Create Customer Report' button to your sub procedure that processes this solution and name the subroutine Sub Create Customer Report(). Make sure the sub is in the Problem1 module.

Answer 1

Assign the below code to a button 'CreateCustomerReport' to get the desired list. It is Raanged for the visible part of the number of rows in the uploaded image i.e from 4 to 23; You can tweak the code as per your requirements.


Sub CreateCustomerReport()
    Dim rngCell As Range
    For Each rngCell In Range("A4:A23")
        If WorksheetFunction.CountIf(Range("B4:B23"), rngCell) = 0 Then
            Range("D" & Rows.Count).End(xlUp).Offset(3) = rngCell
        End If
    Next
    For Each rngCell In Range("B4:B23")
        If WorksheetFunction.CountIf(Range("A4:A23"), rngCell) = 0 Then
            Range("E" & Rows.Count).End(xlUp).Offset(3) = rngCell
        End If
    Next
    For Each rngCell In Range("B4:B23") 
         If WorksheetFunction.CountIf(Range("A4:A23"), rngCell) = 1 Then   
            Range("F" & Rows.Count).End(xlUp).Offset(3) = rngCell 
       End If
    Next
End Sub