COUNTIF help, or is there a better alternative?

[vulture49]

Hi everyone, needing help with my project.

I need to count the ordinal number (1st, 2nd, 3rd, etc) of an entry in a given list. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Smith, John
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Doe, Jane[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Smith, John[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Parker, Peter[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Parker, Peter[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Doe, Jane[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Parker, Peter[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Smith, John[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Doe, Jane[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Doe, Jane[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Parker, Peter[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Smith, John[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Parker, Peter[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Meaning, Parker, Peter - 5 means it's the 5th "Parker, Peter" entry in this table.

Now, the formula I used (for cell B1) is:
=COUNTIF(A$1:A1,A1)

to be dragged down to all cells in column B.


This works perfectly well. But the thing is, I'm doing this for data with more than 5000 entries, and growing. The data in column A are entered through a barcode scanner. A new entry is entered in column A every time a barcode is scanned. Say we're already in 1000th entry. Excel processes a long time (around 3-5 seconds) before you can enter/scan the 1001st barcode. Why is this so? Is there a better way?


You might want to know:
The whole thing is the data source of a PivotTable wherein I only have to count the 1st entry of each unique item in column A. (i.e. only the "1" entries in column B to be counted). Regardless of how many "Parker, Peter"'s there are on the list, the fact that there is one instance of "Parker, Peter" in the table, shall be reflected in the PivotTable as "1".

The whole purpose of this project is to monitor the manpower entering the building. Here's the flow:
Person X scans Barcode ID every entry/exit made into the building (hence creating one new row in the table). Person X can go in and out of the building multiple times. Report shall say that Person X entered the building at least once.

That's the whole point of the COUNTIF. Because if I don't put any counter of some sort, every time Person X will scan the barcode ID will be counted as another entry, which will defeat the purpose of knowing "How many unique people entered the building?"

Please do help me with your suggestions! Sorry if I can't explain myself using a few words. Cheers!

 

[trunten]

Hi,

If you just wanted to count the unique values in column A you could try this array formula:


=SUM(IF(FREQUENCY(IF(LEN(OFFSET(A1,0,0,COUNTA(A:A),1))>0,MATCH(OFFSET(A1,0,0,COUNTA(A:A),1),OFFSET(A1,0,0,COUNTA(A:A),1),0),""),IF(LEN(OFFSET(A1,0,0,COUNTA(A:A),1))>0,MATCH(OFFSET(A1,0,0,COUNTA(A:A),1),OFFSET(A1,0,0,COUNTA(A:A),1),0),""))>0,1))

paste that into any cell on the same sheet as your list of names using ctrl+shift+enter

eg.
Select cell -> Select formula bar -> Paste formula -> ctrl+shift+enter

 

[Oeldere]

This works perfectly well. But the thing is, I'm doing this for data with more than 5000 entries, and growing. The data in column A are entered through a barcode scanner. A new entry is entered in column A every time a barcode is scanned. Say we're already in 1000th entry. Excel processes a long time (around 3-5 seconds) before you can enter/scan the 1001st barcode. Why is this so? Is there a better way?

It is getting slow since excel needs to calculate =COUNTIF(A$1:A1,A1) over and over again.
I should copy => paste special values the values (row) 0 till 5.000.

After that those calculations are gone in the file (for row 0 till 5.000)

 

[vulture49]

Thanks guys for your responses, but still it's not exactly what I'm looking for.

Will there be a difference if use an ARRAY instead of copying the formula to all the cells?

To put it simply, I just need to know "How many unique people entered the building?" Copy -> Paste Special Values is not an option since there won't be anyone to do this. (The program will be left open/operational at the entry point of the building. Reporting will be done at the end of the day.)

(In other words, reentering people should not be part of the count, but are still subjected to scan their barcodes.)