Theory Question about using an Array

[frank_AL]

I have a program I have written for my son that works as expected. At a high level, the program works as follows, there is a Cost file for company products and a file that lists products a given customer buys. Using the test files I was given to work with, the Cost file contains nearly 1,100 records and the customer file contains 280. My current process steps through the records in the Customer file, obtains Product Name, Measure and Container Type. I then filter the Cost file on those 3 fields and make a decision of which record to use to write data over to the Customer file.

As it stands right now, the program takes about 8 minutes to complete and I know the primary reason is the fact that I am applying multiple filters, removing the filter and reapplying over and over again.

I am considering writing the cost file to an Array in an effort to speed up the program. I've used Arrays in the past but have to admit I'm very much a novice when it comes to using and manipulating data in an Array. So I have a few questions:
1. I assume if I use an Array it would significantly speed up the overall process. In general, given the size of the Cost file, would writing it to an Array cut processing time in half? More?
2. Should I continue with the concept of Filtering the Array or is that even necessary? Would the program still run significantly faster if I just step through the 1,100 records rather than apply a Filter?

So any help is much appreciated.

Frank

 

[DanteAmor]

1. I assume if I use an Array it would significantly speed up the overall process. In general, given the size of the Cost file, would writing it to an Array cut processing time in half? More?

It is correct, the process can be faster. It is difficult to determine how long it will be reduced, but even the result could be instantaneous.
I have run processes with over 100,000 records and the process takes just a second.

We could check your code to convert it to arrays. Put here your complete code and a sample of your source data and the result you want.
If your data is confidential, change it to generic data.

Use XL2BB tool minishets to put examples here.

 

[frank_AL]

Thank you for your offer. I have grandkid duty today so I will take you up on your offer but it may take a bit before I can post the code and files as you have requested.
One question. Would it be okay to only provide files with a small subset of the data rather than the entire file? There is confidential data and posting a subset would reduce how much redacting I need to do before sending you files. Let me know and thanks in advance for offering to assist me.

 

[DanteAmor]

Of course, a representative sample of data is sufficient, the sample should contain all the possible considerations or scenarios that are contemplated in your code.
And a sample of the desired result is also important.

Use Xl2BB tool to show examples.

 

[frank_AL]

Of course, a representative sample of data is sufficient, the sample should contain all the possible considerations or scenarios that are contemplated in your code.
And a sample of the desired result is also important.

Use Xl2BB tool to show examples.

Dante, Since I posted I have met with the manager that works for my son to review the program. There's is a bit of an issue with him making changes not understanding how that impacts programming. As a result I am making some additional code modifications. Once I am certain the updated code can be effectively used by this person I will take you up on your offer and post my code and sample data files. Sorry for the delay!