Vlookup NA Count

[niladri2005]

Hi All,

I have two sheets. In Sheet1 I have 2 columns (SalesPersoncode and Customercode). In sheet2 I have the same column. But I here i have the same SalesPersoncode but same or different customercode.

Sheet1

SalesPersoncode	Customercode
E1	ED0002 <colgroup><col width="89"></colgroup><tbody> </tbody>
E1 <colgroup><col width="114"></colgroup><tbody> </tbody>	ED0005

<tbody>
</tbody>

sheet2

SalesPersoncode	Customercode
E1	ED0002
E1	ED0006
E1	ED0007
E1	ED0006

<tbody>
</tbody>

Which I want is that I want unique count of New customer code in sheet2. Here ED0002 customer code I have in Shhet1 so I don't need that. I want only new Customer and it should be the unique. Here in Sheet2 For E1 2 new customercode
(ED0006 and ED0007). I want that count.

Can you please suggest me how I can do that.

Thanks in advance!!

Niladri Sekhar Biswas

 

[pbornemeier]

Option Explicit

Sub CountNewCustomers()
    'Provide a list/count of all Column B on Sheet2 not listed in Column B of Sheet1

    Dim A1, A2, lX As Long
    Dim i1, k1, c1, i2, c2, k2
    Dim lngLastSheet1Row As Long, lngLastSheet2Row As Long

    With CreateObject("Scripting.Dictionary")
        With Worksheets("Sheet1")
            lngLastSheet1Row = .Cells(.Rows.Count, 2).End(xlUp).Row
            A1 = .Range("B2:B" & lngLastSheet1Row)
        End With
        With Worksheets("Sheet2")
            lngLastSheet2Row = .Cells(.Rows.Count, 2).End(xlUp).Row
            A2 = .Range("B2:B" & lngLastSheet2Row)
        End With

        'Add all Sheet2!B:B Items, Row 2 and below
        For lX = 1 To UBound(A2, 1)
            .Item(UCase(A2(lX, 1))) = .Item(UCase(A2(lX, 1))) + 1
        Next
        'i1 = .items: k1 = .keys: c1 = .Count
        
        'Remove all Sheet2!B:B Items, Row 2 and below
        For lX = 1 To UBound(A1, 1)
            If .exists(UCase(A1(lX, 1))) Then .Remove UCase(A1(lX, 1))
        Next
        i2 = .items: k2 = .keys: c2 = .Count
        
        MsgBox "There are " & c2 & " names listed in column B of Sheet2 that are not also listed in Column B of Sheet1."
        
        'Uncomment next block to put list of new names on Sheet3
'        With Worksheets("Sheet3")
'            .Cells.Clear
'            .Range("A1").Value = "New on Sheet2"
'            .Range("A2").Resize(c2).Value = Application.Transpose(k2)
        End With
    End With

End Sub