Help with Arrays

[grey31_99]

My apologies if this doesn't make sense, I am new to using arrays and have been trying to research this but I am quite lost. Please let me know if this does not make sense.

I have the current solver below that I want to update variables on. The first section works correctly for a single solve but I would like it to solve numerous times to get the 2nd, 3rd etc best results. I also want to limit the number of times each value is used, for example in the sample data below, Gary can be used a maximum of 5 times, James 2 times etc. Each can only be used once per solve. When they have been used to their max, they would not be available for any successive solves. I have been told the best way to do this is to use an array and after each solve, reduce the number by one for the values used.

The second issue I am having is reducing the max solved value through each loop, I am assuming the solved for value should also go into an array so that it can be changed as well.

Lastly, for the results, I have been researching and it appears that it would be best to store the results in an array until complete then to add to my spreadsheet instead of each solve.

Can someone provide any insight on how I should approach this?

Please Login or Register  to view this content.

Use Name ID Cost Custom score Include
0 Gary 12472001 11600 227 5
0 James 12472009 9300 220 2
0 Frank 12472017 8500 124 2
0 Matt 12472023 7900 182 2
0 Mike 12472030 7600 156 1
0 Greg 12472031 7600 226 1
0 Henry 12472032 7500 168 1
0 Julia 12472033 7500 132 1
0 Karen 12472034 7500 163 0
0 Sharon 12472035 7400 189 0
0 Anton 12472036 7400 143 0
0 Cheryl 12472037 7400 136 2
0 Joe 12472038 6300 129 1
0 Jenny 12472039 6200 214 4


Expected results
12472009 12472017 12472023 12472030 12472031 12472032

12472030 12472031 12472032 12472033 12472034 12472035

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I have done it for you this time.)

[grey31_99]

Much appreciated, I did not know that was required.

[AliGW]

Perhaps have a quick read of our rules, then. There’s a link at the top of the page.

[Alf]

I think you stand a better chance to get help if you upload a file for forum members to work with.

To upload click on "Go Advanced" button an in the new window that opens scroll down to blue line with text "Manage Attachment". Click on that line and then you get to menu where you first select a file to upload and then click the "Upload" button.

As this is an open forum don't forget to remove any personal or sensitive information from the file prior to the upload.

Alf

[grey31_99]

Attached is a sample set of data. I'm not sure if it would matter but in my actual spreadsheet, the data in Columns B through G are brought in via VLOOKUP.

Let me knw if you have further questions.

Thank you.

[grey31_99]

Just following up to see if anyone has any ideas on this?

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

As you are new here I will add it for you https://www.mrexcel.com/forum/excel-...lp-arrays.html

[grey31_99]

My apologies, I meant to close this thread when I posted to the other forum.