convert formulas to vba - countif and find unique values

Hi

I have a workbook (sample attached) that contains 2 worksheets (Data and Stats) with info re people selecting boxes.

In the Data worksheet I paste the collected data into columns D:G (columns A:C then calculate the month, day and time).

In the Stats worksheet I have 2 columns; column A which contains the Box Reference number and column B which contains the Box Name (box1, box2 etc).

I currently have a formula, in column C of the Stats worksheet, that calculates the number of times each box is selected as follows:

Please Login or Register  to view this content.

I have another formula, in column D of the Stats worksheet, that calculates the number of unique selections of a box by a single person ie if Box2 is selected by Person1 on more than one occasion then that is counted as 1 unique selection. Code as follows:

Please Login or Register  to view this content.

This all works perfectly, but I frequently have up to 30,000 rows in the Data worksheet and the calculation can take up to 30 minutes! What I would like to do is convert these formulas to a macro, which I assume will speed up the calculation time?

Any help would be appreciated....

Thanks

Hi, the formula in column C looks to be overcomplicated to me (although i could be wrong!).

so in cell C1, you just want to know how many times Box Ref 1902 appears in column G on the data sheet?

hmmm, thanks for the replys, but not sure how this helps to speed up the calculations.

I think it is the formula in column d (find unique selections) that is slow to calculate, hence I thought a macro solution would be the best option?

Emma

Hi Emm, try this code

Please Login or Register  to view this content.

Wow, thank you so much John - exactly what I needed...and runs in 10 seconds! Have spent the last hour trying to understand the code...sort of getting there!

Just one thing, how would I ensure that blank cells (eg box7 with no selections) returns the 0 rather than being blank?

Thanks again
Emma

Worked it out...

Please Login or Register  to view this content.

Thanks again John!

Hi, glad to be of help. If there's any part of the code you don't understand pls feel free to ask.

Thanks for the feedback
John