Looping through 50k rows of sales data

[erison]

Hi all. I'm a beginner so I guess this is a beginner question However...

I have made a Macro for a sales rapport, as I want to merge some number from the article list into the sales list. I would be in great help for some feedback.

To begin with, I have one sheet which is a sales reports file with 50000 rows, and I have one sheet with is an article list on 500 rows. The common denominator is the SKU. (Every sales order row has a SKU and every article list row has a SKU.)

Now I would like to add some article info (whether it is a central warehouse product or not) into the sales sheet in order to make stats. Thus, I made a new column in the sales sheet and made a macro.

Basically what the macro does is to loop through all sales 50000 rows, and for each row it checkes for a SKU-match in the article row (loops up to 500 until match). If the CWP-kolumn is 1 in the article sheet, I set the new CWP-column in the sales sheet to 1 as well. If not, I set it to 0. (And if we get no SKU-match at all, it should be 2.)

I have two questions:
1. In this solution, I can only run 5000 lines at a time. Is there a setting to that I could run all the rows in one click? I understnad it's up to 50000x500 rows to go through, but could I make the process to run in a slower pace or something in order not to hang up.
2. Is there a better way of doing this?

Thanks in advance

Slightly simplified, this is my code:

Please Login or Register  to view this content.

[peterrc]

Hi erison,

Can you please attach a copy of your xlsx file.
It it's larger than 1Mb can you provide "cut-down" version, i.e. 1000 rows instead of 50000 rows.

To attach a file to your post,
• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

Regards

peterrc

[erison]

Hi.

I have now attached a file with very little data (as this is quite sensitive) but with the design of the file.

Thanks for pushing me through this, obviously my original code didn't exactly doing what I wanted. I changed the position of one row and now it works (I think):

Please Login or Register  to view this content.

[peterrc]

Hi erison,

In your original post you stated 50,000 rows.
The file you attached has 9 rows plus headers, mainly "x" and single numbers in just 2 coulums, A and C.
Obviously there is nothing to work with.
Have you attached the wrong file.

Regards

peterrc

Edit 1
There are 19 numbers in column L on the Sales sheet but still not enough data to work with.

[erison]

Hi. No this is just a copy in order to show the structure while not revealing the original data. Would it help a lot to add more sample data into it?

[peterrc]

Hi erison,

Yes, it would help, together with a "mock-up" of what you want the Articles sheet to look like when "finished".

Regards

peterrc

[erison]

Ok, I will look for a way to generate sample data then and come back

[xladept]

Try this:

Please Login or Register  to view this content.

[erison]

Great, thank you XLAdept. I am not familiar with CreateObject, but if I read the code correcly this is what happens: In the first loop, all SKUs from the articles are put in a dictionary. In the second loop, .Exists checks if the actual Sales SKU is in the dictionary and sets the number to one or two.

This code is definitely prettier, but does it make fewer operations than mine? (I don't try to be critical, just curious.) Or does something else makes it faster? (Maybe going through a dictionary is faster than looping through cells?)

[xladept]

It's faster because the sales sheet is transformed into a worksheet array making each operation occur in nanoseconds rather than microseconds!

[erison]

Ah ok, I see. Thanks

[erison]

Does anyone know the answer of my question no1? Is there a way to make excel run in more of a marathon tempo than 100m tempo in order to complete very heavy operations (while doing it over more time). Thanks

[xladept]

Is this still too fast?

Please Login or Register  to view this content.

[xladept]

You're welcome and thanks for the rep!