View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default Counts unique values based on 2 criteria in other columns (200.000 records)

Hi Johan,

Am Sun, 10 May 2020 10:22:11 -0700 (PDT) schrieb JS SL:

It runs very fast. 13000 records in a few second and the results are correct.
But..... after those 13000 it stops with an error on;
" myRng = .Range(.Cells(i, "E"), .Cells(Last, "E")).Address "

When using your earlier code it stops with an error on;
" Res = Evaluate("=Sum(1 / CountIf(" & myRng & "," & myRng & "))") "

After some investment I see that it stops when the WeekNumber become by the value 18. I'd changed 18 till 17, no problem. I'd changed it to 19... oeps. Everything which is identical/higher in column Q then 18 gives the run error.


for me it works with all weeknumbers, also with numbers greater then 18.
That the code runs correctly the table must be sorted by weeknumber and
name.
If that doesn't help send me a demo workbook.


Regards
Claus B.
--
Windows10
Office 2016