VBA code to count distinct values based on multiple criteria

Question

I have a data sheet "orders" which has data in the format

order no    Customer    Sales Executive Order Status    Order Date
211 nokia   john    cancelled   23-May-13
643 panasonic   andrew  fulfilled   23-May-13
209 samsung john    fulfilled   4-Apr-14
453 philips andrew  fulfilled   4-Apr-14
311 dell    mary    fulfilled   16-Apr-14
865 panasonic   andrew  fulfilled   16-Apr-14
201 apple   john    fulfilled   3-May-14
453 hp  mary    cancelled   3-May-14
205 nokia   john    fulfilled   4-May-14
643 philips andrew  fulfilled   4-May-14
312 lenovo  mary    fulfilled   22-May-14
204 apple   john    fulfilled   7-Jun-14
432 hp  mary    fulfilled   7-Jun-14
214 nokia   john    pending 25-Jun-14
754 panasonic   andrew  fulfilled   25-Jun-14

Above are the columns that matter out of the many columns in the order sheet.

I have another worksheet where I have the "Sales Executives" listed and want to know how many unique customers they had orders fulfilled by month

Sales Executive Apr-14  May-14  Jun-14
john    <value> <value> <value>
mary    <value> <value> <value>
andrew  <value> <value> <value>

I want to code to read the sales executive name in the row and month in the column and then give the answer like below

Sales Executive Apr-14  May-14  Jun-14
john    1   2   1
andrew  2   2   1
mary    1   1   1

I am looking for a vba code that can run this on a monthly basis. The above example is a sample set of the actual data.

I am relatively new to VBA and need help with the code.

It would be helpful if I get an explanation on the code as how it work as I need similar code to find how many products and total revenue generated by each sales executive for the months.

Thanks for your help in advance

EDIT (code from OP comment below):

Sub UniqueReport() 
Dim dict As Object 
Set dict = CreateObject("scripting.dictionary") 
Dim varray As Variant, element As Variant 
Dim lastrow As Long 

lastrow = Sheets("Orders").Range("N" & Rows.Count).End(xlUp).Row varray = Sheets("Orders").Range("N2:N" & lastrow).Value 

For Each element In varray 
    If dict.exists(element) Then 
        dict.Item(element) = dict.Item(element) + 1 
    Else 
        dict.Add element, 1 
    End If 
Next 

ActiveSheet.Range("P2").Value = dict.Count 
End Sub 

Answer 1

You can do this without VBA just using Worksheet Formulas and Functions.

On your main data sheet, add a column next to the date column and in the cell for row 2 paste this formulal:

=TEXT(E2,"mmm yyyy")

this will make the month and year available for a comparison

Next on your Sales Executives worksheet, enter this formula in the cell B2

=COUNTIFS(Orders!$C2:$C$16,$A2,Orders!$F$2:$F$16,B$1)

so what is this formula doing?

In the first criteria we specify that we want to compare the name in A2 against all the values in column C:C, AND we want to compare the month in ROW A against the TEXT month we have placed in column F on the Orders sheet.

Then you can drag the formula across for the months that you need, and down for each Sales Executive

Also, see:

• Excel COUNTIFS Function
• Excel COUNT Functions

EDIT:

You could also do this programmatically:

1. Add a dummy column with TEXT formula to give a Month and Year date for comparison on the Orders worksheet.
2. Add the formulas on the Sales Executives sheet
3. Copy paste values over the formulas
4. delete the dummy column

try this:

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

With Sheets("Orders")
    .Columns("E:E").Insert Shift:=xlToRight
    .Range("E2:E" & .Range("D2").End(xlDown).Row).FormulaR1C1 = "=TEXT(RC[1],""mmm yyyy"")"
End With

With Sheets("Sales Executives")
    .Range("B2:D" & .Range("A2").End(xlDown).Row).FormulaR1C1 = "=COUNTIFS(Orders!C3,RC1,Orders!C5,R1C)"
    .Range("B2:D" & .Range("A2").End(xlDown).Row).Copy
    .Range("B2:D" & .Range("A2").End(xlDown).Row).PasteSpecial Paste:=xlPasteValues
End With

Sheets("Orders").Columns("E:E").Delete Shift:=xlToLeft

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

So all you need to do is step through the code using F8 to see what it does.

Another option of course is to make the calculations in your SQL Server query that retrieves teh data using the SQL PIVOT function.

Hope that helps