Count Unique Values including codes (numbers and letters)

[msv785]

I have a data set of 100k rows and i need to count the codes (including numbers and letters) of 1 column. The problem is that the model is all linked - and i will keep adding raw data on the sheet, so i am trying to find a formula i can drag down - help would be much appreciated

 

[jproffer]

Maybe if you include some sample data (please, not 100,000 rows ) so we can see what you mean. A couple rows of what you have, along with the expected results of this formula you want.

 

[Aladin Akyurek]

I have a data set of 100k rows and i need to count the codes (including numbers and letters) of 1 column. The problem is that the model is all linked - and i will keep adding raw data on the sheet, so i am trying to find a formula i can drag down - help would be much appreciated

Suppose that that 1 column is column A with a header in its first row (cell).

In C1 enter:

=MATCH(9.99999999999999E+307,A:A)

In C2 enter:

=MATCH(REPT("z",255),A:A)

In C3 enter:

=MAXIFS(C1:C2,C1:C2,">0")

In C5 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A2:INDEX(A:A,C3)=""),MATCH(A2:INDEX(A:A,C3),A2:INDEX(A:A,C3),0)),ROW(A2:INDEX(A:A,C3))-ROW(A2)+1),1))