Count number of dates mentioned more than 10 times in a column

[Hansen]

Good morning everyone,

Hope you are all doing good.

I have an excel table with order numbers in column A and dates of the order in column B. Usually this table holds between 20.000 and 40.000 different orders for a month, so with around 25 different dates.

Basically I want to know on how many days we received orders, but I only want to consider days where we received more than 10 orders (for example on only 18 days out of the 25 different days, 10 or more orders were generated. Then I want to receive the number 18).
I know how to solve this problem with a pivot table, but want to do it using a VBA macro.
Any input on how setup a micro to do this would be highly appreciated.

Thank you!

[Bob Phillips]

I would write the macro to add a helper column with a formula to count them,like so

=--(COUNTIF(B$2:B2,B2)=10)

and then simply sum that column.

[Paul_Hossler]

If you want a pure VBA sub, something like this

I only had 1000 orders in my test but it finished 'at once'




Option Explicit

Const cThreshhold As Long = 10

Sub CountDates()
Dim rDates As Range, rDate As Range
Dim aCounts() As Long
Dim iDateMin As Long, iDateMax As Long
Dim i As Long, n As Long

Set rDates = ActiveSheet.Cells(2, 2)
Set rDates = Range(rDates, rDates.End(xlDown))

iDateMin = Application.WorksheetFunction.Min(rDates)
iDateMax = Application.WorksheetFunction.Max(rDates)
ReDim aCounts(iDateMin To iDateMax)

For Each rDate In rDates.Cells
n = rDate.Value
aCounts(n) = aCounts(n) + 1
Next
n = 0
For i = LBound(aCounts) To UBound(aCounts)
If aCounts(i) >= cThreshhold Then n = n + 1
Next i

MsgBox "There were " & Format(n, "#,##0") & " dates with more than " & cThreshhold & " orders"
End Sub




No real error checking, It assumes that the date column really does have dates from row 2 to N, i.e. not blank and a real date

[Hansen]

Thank you guys for your replies.
Both options work fine, still I opted for yours Paul, since I can easily adjust the count threshold and dont need to write any formulas in my spreadsheet.

Thanks again!

[Bob Phillips]

Not trying to get you to change your mind, but I was suggesting using VBA to inject those formulas, you wouldn't have put them in, and you could have put a threshold in there just as Paul did.