How can I make this array formula (see thread) less computationally demanding?

[Dexamphetamine]

Hi all again! (Still working on aspects of the FDA adverse events database)
I wish to find the Patient ID (ISR) that occurs in a list (only 2 columns).
The following array formula that I came up with:

{ =COUNTIF(A2:A1000,INDEX(A2:1000,MATCH(MAX(COUNTIF(A2:1000,A2:A1000)),COUNTIF(A2:A1000,A2:A1000),0))) }

works a treat on a list/file with around 1000 rows, however the files I am playing with have around 300,000 rows and it takes far too long (not bothered to try to wait - gave up after 10 mins).

I am sure there must be a much more computationally elegant and less resource intense way of performing the same calculation - Likely VB?. - Please suggest.

Attached is a sample of the first 1000 rows for testing.

[oeldere]

Maybe pivot table and sort from high to low.

[Dexamphetamine]

Thanks oeldere. I have to admit that I have not used pivot tables much in the past, looks like this will be a good chance for me to learn. Thank you for your valued input!

[oeldere]

Thanks for the reply.

Glad I could help.

[ARGK]

Hi
I have just played around with your sample and tried it with over half a million rows and it works fine. The only thing I changed was to sort the data by Column A. It took about three seconds.
On that basis make sure your data is sorted by Column A and give that a go.
Good luck.
Tony

[Dexamphetamine]

Thanks ARGK,
One question, what do you mean by "sorted by column A"?

[ARGK]

Hi
You need to sort all of your data based on the values in Column A. So highlight all your data and click on sort (click on the A to Z button and by default it should sort by the first column in the range, which in your case will be column A.
Tony

[Dexamphetamine]

Thanks ARGK,
I agree the sorting is required, and have always sorted the data prior to processing for that exact reason (have been using $LOOKUP functions a bit lately). I was hoping that there was something else that I may have missed, but it appears that my computer is just too slow. This time I let it run for nearly an hour without completion. Definitely not going to sort though another 40+ files this size using my array formula.
Thanks for the help all the same.
Dexamphetamine