Trying to search for list of strings using Arrays,Scripting Dictionary or FileSystemObject

I have been able to achieve this many different ways, but my methods become obsolete if there are too many rows.
My first attempt was something like this:

Please Login or Register  to view this content.

This worked fine for smaller groups of data, but with 150,000 rows, this code brings my computer to its knees. I also tried using .Find, and it got even slower.. My final attempts were with Scripting.Dictionary and FileSystemObject, seemed as if they would have worked much better, but I couldn't quite get either to work correctly.

So if anyone can code a search that can look through > 200,000 rows
-Find a match,
-Select the first blank row on Results Sheet,
-Return the entire row of corresponding data from sheet1,
-Skip a column, and continue adding the entire row of corresponding data from sheet2 (so that everything is on the same row)
-repeat for all occurrences of all strings in ColumnA of a list sheet
it would be greatly appreciated!

That's really close, looks like matched rows from sheet2 are being returned, but nothing from sheet1

Please attach a sample

I wrote the attached to a question on this forum. The question and format of the answer appear to be very similar.
Try it

I wrote the attached to a question on this forum. The question and format of the answer appear to be very similar to your request.
Try it

Sorry, Should have done that from the start:

That one is close too.. Maybe the code i have been using can explain it better than i can word it:

Please Login or Register  to view this content.

Have you tried my code?

Hi downtown1933,
try it

Please Login or Register  to view this content.

downtown1933,
My code works on a single row as per your description, but Nilem came up with a better and accurate code.

nilem & jindon, both codes worked perfectly on the sample data, but when tested on my actual data, Excel froze up and restarted itself... I cant seem to find anything that can handle processing that many rows!?

My actual data would be about the same as the sample data, only differences really being that the Actual ListSh has about 1700 Rows, and the Actual DataSh has about 7,000 Rows.. I know that in the end, when matched correctly there should be well over 100,000 on the results sheet... It just seems like anything i try brings my computer to its knees

downtows1933,
100k is a massive data, but I would not expect my PC to freeze. Yes, the code may take a couple of minutes to run, but not to freeze.
What is your memory gig?

Got a Type Mis Match Here:

n = n + UBound(.Item(a(i, 1)), 2) + 1

Not sure how would i check?

Are the data layouts the same?

The data should start from A1.

I got it running, but its been going for 9 minutes now un-responsive? Im not sure if it's frozen or still running?

Hi downtown1933,
could you upload your real file to the file sharing and show the link here?

downtown1933

This took 26.6250 sec for 25727 rows in Sample List.
So, it may take much longer if you have many rows in both sheets.

Please Login or Register  to view this content.

26 Seconds!? Is it something I'm doing wrong? I tried running yours jindon first thing this morning.. It has been going for about 6 minutes now..Right now I've got about 1500-1600 rows in my list, and 5540 Rows in my Data.....


Ok so while I was writing this, it stopped running after about 6 1/2 minutes with an error on this line:
" Sheets("sample results").Cells(x, 1).Resize(n, UBound(b, 2)).Value = b "

snb Yours ran pretty quick, but not quite everything was returned.

26 sec is made under

25272 rows in Sample list
39 rows in Sample Data
End up 172460 rows in result.

1500 * 5540 = 8310000 (maximum possibility)

It easily exceeds the row limit of 1048576.
Exceeds the row limits in Excel.

I suspect your data are loaded from an application, such as SAP, or oracle. In other words, you data are not originally created in excel, rather they are exported in to excel from external application. If my assumption is right, even 20k rows will crash excel.
That is my experience any way. You need to convert these data in VALUES ONLY FIRST before you work on them.

jindon,
i tried your "book2 with code2" again. I added values to Column H in both sheets and it started running..
I still got the error in line 34 here: " n = n + UBound(.Item(a(i, 1)), 2) + 1 "

However, There were about 25000 rows that were still sent to the results sheet prior to the error?

AB33, are you saying to just copy/paste as values before running? And just for future reference, why would pasting everything as values make a difference?

I do not know the reason, perhaps more experience people can suggest possible reasons.
I was answering a thread, similar question to this. The OP had e-mailed me his spread sheet. He had about 20k rows, but when I run the code, it kept crashing my PC- Infact 3 times. I thought I lost my laptop. My laptop was making lots of noise when I run the code. My laptop is prey new. 8 Gig memory.

I copied the sheets and converted the data in VALUES ONLY, deleted the source sheet. Boy, the code run in 5 seconds.
I had similar other experience in this site after the first incidence.
If I run a code and PC starts to make noise, I know these data are imported one. This is just my experience in this site.
As I said, I have no idea what causes this behaviour, but I suspect these data may have unseen dirty data.

Originally Posted by downtown1933

jindon,
i tried your "book2 with code2" again. I added values to Column H in both sheets and it started running..
I still got the error in line 34 here: " n = n + UBound(.Item(a(i, 1)), 2) + 1 "

However, There were about 25000 rows that were still sent to the results sheet prior to the error?

I have just tested the file with 25272 rows of data with Col.H added to both sheet.

It run without error.

Make sure data starts from A1 in both sheet.

jindon,

To add to the mystery of this error i keep getting; I pasted a smaller sample of my data to your "book2 with code2" and ran it again. This time it executed perfectly, with no error?

Every time I run it, I remove any blank lines, and sort Col-A values from smallest to largest. The only variable which has changed here is that i used a smaller number of rows. Could it be the size?

I think we are talking about different codes.

I'm running the code in my post #23
that is

Please Login or Register  to view this content.

It looks like the error goes away if i remove all blanks, and remove all Col-A values that have letters & Numbers.. (ie: "API1000023456")
Would that make sense? Should the cell value have any impact on the results?

That's crazy because I have come across similar issues. I don't recall what exactly it was that I was trying to accomplish, but my PC has crashed while trying to run very simple code. I don't remember how big the file was, but I do remember the noises it made right before doing so...

Do you remember where your data came from before you put it in excel?

Originally Posted by jindon

Blanks are no good, but the "API1000023456" should not affect the result.

Need to see the actual data type anyway if possible.

P.S.
Past midnight, so I will see you tomorrow if any problem left.

Thanks jindon! There was some special character on the end of some of my Col-A values.. I took those out and it works Great. I also added a timer, With a list sheet containing about 1600 values, being matched to Data Sheet with about 6,000 values, it pulls almost 170,000 rows to results sheet, in 6.58 minutes. This is the code that did it courtesy of jindon:

Please Login or Register  to view this content.

That's good to hear.

That's all I can do for you.

If the code solved your problem, please mark this thread as "Solved"

jindon, one more thing, would you mind commenting your code? I'm still a bit new to the Scripting.Dictionary and I want to understand what exactly you just did. Thanks again.

1)

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

Help?