Multiple Condition Countifs with Uniqueness as Condition

For some reason I can't attach a file. Here's a link:

https://www.dropbox.com/s/sed7iajd3w...mple.xlsb?dl=0

Hi,
Calculating with formulas can be really slow on 800k records.
Faster way to manage it - using VBA array with Scripting Dictionary. Looping through array is much more faster that calculations on worksheet, and Dictionary can be useful to detect duplicate records.
Please write back if it's OK to solve it with VBA (not formulas), then we could firure out some code.

Originally Posted by kasan

Hi,
Calculating with formulas can be really slow on 800k records.
Faster way to manage it - using VBA array with Scripting Dictionary. Looping through array is much more faster that calculations on worksheet, and Dictionary can be useful to detect duplicate records.
Please write back if it's OK to solve it with VBA (not formulas), then we could firure out some code.

Yes, that's okay. I can work with VBA, but this is a conceptually difficult problem for me.

Ok, try this code. It should calculate count of users with 2 or more "dog" transactions before 08/31.
Could you please describe more in detail what do we need to calculate next? For 09/30, 10/31 and so on?

Please Login or Register  to view this content.

double post

Originally Posted by kasan

Ok, try this code. It should calculate count of users with 2 or more "dog" transactions before 08/31.
Could you please describe more in detail what do we need to calculate next? For 09/30, 10/31 and so on?

Please Login or Register  to view this content.

That worked beautifully. Thank you so much.

The next step is to say, okay, of that 1320 people that purchased dog >= 2 times before 8/31:

How many of those 1320 purchased dog >= 1 time in the last 60 days from 9/30? (i.e. from 8/1 to 9/30). How many from 9/30 purchased dog >= 1 time in the last 60 days from 10/31? (i.e. 9/1 to 10/31) Basically a moving 60 day window that ends on the next EOMONTH date. Keep doing that until the data ends on 1/31/17.

Try out this code. It should calculate all figures you need.
Before you run the code fill dates of first days of the month in row 6. G6 = 2016.08.01, H6 = 2016.09.01 and so on.

Please Login or Register  to view this content.

Originally Posted by kasan

Try out this code. It should calculate all figures you need.
Before you run the code fill dates of first days of the month in row 6. G6 = 2016.08.01, H6 = 2016.09.01 and so on.

Please Login or Register  to view this content.

I would hug you if I could. Thank you so much.