0

problem i have 300.000 entries

Colum A = YEAR
Colum B = month
Colum C = customer number

G5 to G13 = criteria

I want to count how many unique customers pr. year - I tried using an array formula - but that is no good on such a huge data set... i guess...

formula used

=SUM(--(FREKVENS(HVIS(A:A=G5;SAMMENLIGN(C:C;C:C;0));RÆKKE(B:B)-RÆKKE(C1)+1)>0))

or in english:

=SUM(--(FREQUENCY(IF(A:A=G5,MATCH(C:C,C:C,0)),ROW(B:B)-ROW(C1)+1)>0))

This works fine just fine if i have a small data set - but with my dataset it just starts to calculate and then stay at 100% cpu for hours

So what do I do ?

  • 1
    You might want to consider moving this sort of dataset to a database technology and get the DB server to do the heavy lifting on calculations. – Burgi Mar 7 '16 at 15:49
  • Could you use SUBTOTAL? – Burgi Mar 7 '16 at 15:49
0

Using DB is not an option - as others should be able to use this :(

Solution i made is not pretty but it works

1) Make a copy of all customers and remove duplicates 2) use VLOOKUP to find year (since the list is sorted reversed it will get the first time i see a customer) 3) now i do a simple countif with the year as criteria.

Not pretty especially because i had to copy 2) down 465.451 rows

  • Using a DB might not be an option, but it is the right (tm) way. Excel has grown a lot from a simple spreadsheet to a huge much capable program, but with these kinds of data you really want a full database or at leats a light db such as access. (Writes the person who recently hit the 1.8mil, lines in excel limit...) – Hennes Mar 9 '16 at 8:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.