Vlookup NA Count

niladri2005

Board Regular
Joined
Sep 21, 2012
Messages
109
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top