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

Welcome to Stack Overflow. Please read [Stack Overflow: How to ask](http://stackoverflow.com/questions/how-to-ask) and [Jon Skeet's Question Checklist](http://blogs.msmvps.com/jonskeet/2012/11/24/stack-overflow-question-checklist/) to find out how to ask a good question that will generate good useful, answers. — Our Man in Bananas, Aug 08 '14 at 08:31
you don't need VBA to do this, you can do it using worksheet formulas on your *Sales Executives* worksheet. see [Excel COUNTIF Function](http://blogs.office.com/2012/05/04/count-values-that-meet-a-condition-with-the-countif-function/) and [Excel COUNT Functions](http://spreadsheets.about.com/od/excelcountfunctions/) — Our Man in Bananas, Aug 08 '14 at 08:34
You don't even need to write code. You can record macros with the corresponding assistant. Start recording, auto filter your columns and create a pivot table to [count the distinct occurences](http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables-finally-in-excel-2013/). You can do all the copy, paste and formatting stuff you need to create a suitable result. Then stop recording and there it is: Your macro - without writing any line of code yourself ;-) — Jan Rothkegel, Aug 08 '14 at 08:43
I am aware that this can be done using array formulas in excel. But I am building this report where the data/orders are exported from the web on a monthly basis. Somehow the array formulas fail to work when a new data gets replaced in the orders worksheet. I have tried a code using scripting dictionary. It works well to find the unique values in a given column ( here the Customer column). I dont know how to apply the condition on sale executive, month and order status. — user3921129, Aug 08 '14 at 08:58
@Philip - I am aware that this can be done without code. But those array formulas are not helpful when I am running this excel dashboard where the source data is linked to data server and gets exported to a worksheet in excel. So, I am looking for vba code — user3921129, Aug 08 '14 at 09:03
Sorry, Just getting used to SO. This is the code that I have tried http://stackoverflow.com/a/9678944/3921129 — user3921129, Aug 08 '14 at 09:11
@user3921129: check my updated answer which will give you your data programmatically without using a loop or dictionary. — Our Man in Bananas, Aug 11 '14 at 08:36

Our Man in Bananas · Answer 1 · 2014-08-08T10:11:09.960

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:

EDIT:

You could also do this programmatically:

Add a dummy column with TEXT formula to give a Month and Year date for comparison on the Orders worksheet.
Add the formulas on the Sales Executives sheet
Copy paste values over the formulas
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

I will not be able to make any changes to the "orders" worksheet as the data here gets dumped using Apache POI into the excel file. So I cannot add a column to the source data. — user3921129, Aug 08 '14 at 09:18
your macro could add a temporary column then remove it afterwards once the calculations are made - just remember to copy paste the values in your *Sales Executives* worksheet **before** you remove the extra column. — Our Man in Bananas, Aug 08 '14 at 09:25

VBA code to count distinct values based on multiple criteria

1 Answers1