Unique Counts with Multiple Criteria Very Slow! Please Help.

[Geoff.]

Hello,

Any help would be appreciated.

I have a workbook which processes and analyses 5k to 20k lines rows of data at a time through macros. Unfortunately, the macro runs very slowly (5-20 minutes)… Trying to find the bottleneck, I found that a couple of formulas are slowing everything down:
The formulas which calculate unique instances of a combination of 'person', 'week', and 'area'. See below:


Unique Persons and dates combinations only

Please Login or Register  to view this content.

Unique Persons, dates, and area combinations

Please Login or Register  to view this content.

Any way to make these more efficient? Or less resource intensive. I attached an example workbook in case it could help (Attachment HERE: uniqueCount.xlsx)
I read that using SUMPRODUCT might be quicker but not by much, would VBA be better? I saw someone create a user defined function for something similar but it was too different from my situation for me to be able to use.

Thanks,

Goeff.

[FDibbins]

1. wht the need for dymanic ranges? why not just use regular ranges?
2. not sure if you need a unique count? I used this...
=COUNTIFS($D$29:$D$3000,G29,$E$29:$E$3000,"<="&I29,$E$29:$E$3000,">="&H29)
and this...
=COUNTIFS($D$29:$D$5000,G29,$E$29:$E$5000,"<="&I29,$E$29:$E$5000,">="&H29,$C$29:$C$5000,$B$15)
and got the exact same answers you did?

[Geoff.]

Thanks Ford. I wondered if that might do the trick, I tested it out in the original file and indeed I got the same numbers but then I realized from your following question that my example file was missing something important. See below.

2. not sure if you need a unique count? I used this...

'Persons' can have multiple entries per day and that wasn't reflected in the file. I modified the file so that the same dates appear more than once. I attached the new file here (ATTACHMENT: uniqueCount(corrected).xlsx ). I tried your formulas but they didn't work (Apologies for not getting it right the first time!)

The reason I used dynamic ranges is because the length of data being processed is variable, could be 500 rows or 20k rows.

Essentially what I am trying to end with are the unique number of days that a person's name appears during a week (or another date range), and sometimes needing to know this information only for a particular area.

Do you see my problem? Any ideas?

Thanks,

Geoff.

[Geoff.]

Does anyone have any other suggestions?
Thanks,
Geoff.