Calculating Frequency of visits/usage at a car park

Hi Guys

Any ideas on this will be much appreciated. I'm looking at calculating the frequency of visits( once, twice,....) to a car park by customers( represented by vehicle registration number) in year. The visit must be unique/distinct to each day, that is, if a customer visits many times in a day, it is considered as a single day visit.Also under each frequency I want to know the total minutes spent and total cost of parking. The data is massive so I'm wondering what will be the best approach, using formula or pivot?


Many thanks

When providing a workbook, it needs to include some expected outcomes - please add some to your attachment. Do I assume correctly that the X across the top represent numbers of visits? What happens if there have been more than five?

VBA solution .....

Please Login or Register  to view this content.

Click RUN to execute

It will need modifying to account for variable number of registrations: either a given maximum or a "dynamic" calculation of rows need for results table.

I recommend having the "input" and "output" on separate sheets.

Note: the "Input" data is sorted by Registration then Date.

Many thanks John. Much appreciated. I have a follow on question if you don't mind. What will be the script if the input and output references change as follows:

OUTPUT SHEET
Sheet1: A14:K575770

INPUT SHEET
Q1 = B3:B788508
Q2 = B3:G802348
Q3 = B3:G799322
Q4 = B3:G786563

I'be made these changes because the annual data is very large so I've divided it 4 quarters in 4 separate sheets. As advised, the output data is in sheet1 alone.

Thanks

Do you have any idea of the maximum number of registrations in a quarter?

Are you saying there is > 550,000???

And would it be OK to put Total row at head of output table i.w in row 14: saves scrolling down 100's of rows!

or better only place totals in "Summary".

Volume tests on the original code took forever so now modified the code to use "in memory" processing.

This processed over 560,000 records with 530,00+ VRNs in about 10 seconds.

I can't post the file with volume data as it is far too large.

Please Login or Register  to view this content.