Creating Row Index Searching Large Data Set Work Sheets

[slpgma]

I am trying to iterate through a data set which has a large number of rows (> 200k). In the data set there is a ID column which I need to use to find values in the other columns of the data set which match that ID. I would then perform analysis on the column values for all of the row that match that ID.

Right now I am looping through the data and creating a worksheet index to determine the worksheet rows each ID is located. I would then use that index to do a lookup for the column data based on the ID. Even on small data sets ~4k rows it takes forever to get this index created.

I am keeping all of my information in memory so I did not want to use the advanced filtering to filter my records on the sheet and then process.

There has got to be something I am missing get a range of data returned based on the value in the ID column.

Thank you

[bakerman2]

Post your current code.

[Mumps1]

I noticed that there are duplicate ID's (2320). How do you handle the duplicate values? Also, it would help if you could explain in more detail exactly what you want to do. Explain referring to specific cells, rows and columns using a few examples from your data. What do you want to do with the data once a match has been found?

[slpgma]

This is similar to what I am doing in the actual project. My thinking is this is faster to create an index so I can then do a direct lookup of the work sheet data. As an example one work sheet has 4000 rows but only 1500 unique IDs. So it would be faster for me to find the unique id and process the rows that it occurs in.

Please Login or Register  to view this content.

[slpgma]

I incorporated using a dictionary object into the code and it is much quicker. If there is something else which might be a better option please let me know.

Please Login or Register  to view this content.

[bakerman2]

See if this makes a difference.

Please Login or Register  to view this content.

[slpgma]

Adding the range to the array is a big help. I assumed worksheet.cells was loaded into memory.

[bakerman2]

Glad to help and thanks for rep+.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.