Match and Countif within Array

I've got a fairly large (~230k rows) data set, with ID numbers in Column A. The ID numbers are not unique, a particular ID could appear once in the dataset, or it could appear 20 times. As part of my process, I'm sorting by the ID number, then I need to find the first row number that a particular ID appears on, and the number of times that it appears in the dataset.

Right now, I'm doing that in a For loop, and it's painfully slow.

Please Login or Register  to view this content.

I've dumped the data into an array, but the Match function doesn't seem to work the same way within the array as it does when I'm just looking at the Range. It's returning all zeros, probably because I don't have the right syntax to look at all rows (rather than just the RowCountVariable row) of the array. The Red text is where I'm going wrong. (And I haven't even tried the Countif line yet, because I can't get the Match, and that's necessary for the other)

Please Login or Register  to view this content.

Hello SW:
There are 3 things you can do to make this much faster.
(1) Minimize your search area / id. Since you know that your max rows for each ID (i.e. 20) AND that they are sorted THEN only search 20 rows .
This is pseudo indexing of your data and should make a tremendous difference in your time (SEE CODE BELOW FOR EXAMPLE OF 20 ROW SEARCH RANGE)
(2) Write your results to an array and after 10,000 entries to array then dump to sheet and keep doing this until done . Dumping to sheet only once every 10,000 results will make a tremendous diffence !
(3) Turn off screen updating and autoCalculation during process ... if you use the two items above this will make only a small difference.

SEE VERSION 2 & 3 in seperate posts below this one


NOTE: Since you did not provide a data sample I had to make some assumptions . If you provide a data sample I might be able to refine the approach.

Please Login or Register  to view this content.

Originally Posted by SyracuseWolvrine

I've got a fairly large (~230k rows) data set, with ID numbers in Column A. The ID numbers are not unique, a particular ID could appear once in the dataset, or it could appear 20 times. As part of my process, I'm sorting by the ID number, then I need to find the first row number that a particular ID appears on, and the number of times that it appears in the dataset.

Do you need to do this with array?
If you need it to, you will need to loop.
Use of Excel function in vba.
Assuming E1 has ID to be searched.

Please Login or Register  to view this content.

SO I RAN CODE ON 250,000 RECORDS THAT CONTAINED 16671 ID'S (total run time 1.82 seconds)

If the code searches the entire range for each ID then the process takes 3 minutes and 33.92 Seconds ie. 3:33.92 ( i.e. your conventional method)
BUT ... Since you never have more that 20 of any ID, then change the code to search small areas and then the code takes only 1.82 Seconds ( see pseudo index method in code below)
IN SHORT : pseudo indexing data has made the search 11703.30% more efficient.


This is without Arrays or even turning off display updates or autocalc .

Complete code is ...

Please Login or Register  to view this content.

ASSUMPTIONS:
- ID'S sorted prior to running macro
- sheet name is TestData
- Data in column A of TestData sheet

Note: No additional column of ID's are required as they are picked out of the complete raw data column.

IF ID is sorted in ascending order try this code to get First and Last row of ID.

Please Login or Register  to view this content.

VERSION 2
Processes 250,000 ID records in 1.6 SECONDS
added lines to decrease background overhead (e.g. calculation:=Manual, screenUpdate:=off)
added a fourth column of output to include frequency of each ID
added some more comments to explain code

ASSUMPTION: ID's have been sorted prior to running macro
APPROACH : Minimize search range of each ID to speed up macro ( RESULT WAS 11703.30% more efficient ie. run time 1.6 seconds vs 3.5 minutes)

Please Login or Register  to view this content.

VERSION 3 (run time 1/4 second )
Processes 250,000 ID records and saves 16668 records to sheet in: .21 of a SINGLE SECOND

APPROACH :
(1) Minimize search range of each ID to speed up macro ( RESULT WAS 11703.30% more efficient ie. run time 1.6 seconds vs 3.5 minutes)
(2) Use custom dump class to Dump all results to spreadsheet in a single transfer instead of 16668 individual actions (RESULT run time decreased to .21 second)
Custom "dump class" (i.e. ClsBulkUpdaters) collects results to array and ONLY dumps data from array to sheet when macro has completed entire collection of all results.
NOTE: with bulk dumps to sheet screenupdating and calculation DO NOT need to be tampered with or turned off

DEMO ONLY:
This version uses my custom "dump class" (i.e. ClsBulkUpdaters) which is not provided in this sample ... this version is provided to demonstrate how bulk dumps can speed up macros and that turning off applications features is not always needed.
It also demonstrates the principle of abstracting reusable code into it's own class so that it is a quick and simple object to reuse without much complication or fuss.
IN SUMMARY USING VERSION 2 above will be still under 2 seconds and probably be timely enough for your needs.

Please Login or Register  to view this content.

You are welcome and thanks for the rep.

Execution of all my codes on 250000 lines was within 0.5 seconds, especially "IfSorted" is too fast to measure, btw.

Hello Jindon , Good-day
The original poster states

a particular ID could appear once in the dataset, or it could appear 20 times

That would mean then number of unique ID's you test with for 250000 records is 12500 unique ID's, not 1668 . That's what I tested you code with and got an impressive time of 1.6590 seconds .
But the original poster also wanted "the number of times that it appears in the dataset". When I run your "IfSorted" code I'm not seeing a return of the frequency of each ID.
In fact I'm getting the same number for the first and lastrow number and it's neither . (please see screen shot below).
I had to modify your code to run for all unique ID's ... did I do something wrong with you code (Please see attached code)

Please Login or Register  to view this content.

Attachment 780896

SearchResults.jpg