Optimizing a fantasy baseball lineup

Hi zigzagman and welcome to the forum,

Find the attached where I've done these things....

1. Created a table in N18:Q25 determine the start and end of each player position row.
2. In O3:O10 I then did a random lookup of a player of that type using the table above and put there name in Col O
3. I used VLookup from Column O to display their pay and XFactor.
4. I then summed the new players salary and XFactor in Row 11.

Now - here is the cool part. Every time you press F9 it will recalculate and pick a new team because of the RandBetween formulas in Column O. Try it a few times.

I did a macro record to copy what is picked from Columns O:Q over to Columns T:W

I then wrote a very short VBA code to copy the pick to the columns to the right IF the salary was greater than Zero and the XFactor was greater than what was already there. Click on the "New Pick Try" button a few times to see this work. Then put a Zero in W11 and do it some more times. It will only copy the new randomly picket team over if the XFactor is greater than the best previous pick.

Then that was all pretty easy so I wrote a little loop to do the above 5000 times. It takes about 3 seconds to do 5000 random picks and only remember the best one. You can do another 5000 in 6 seconds.

Did I do any wonderful logic to make sure my team pick was the best? Heck no, but I did 10000 random picks and only kept the best one. I guess the logic is that the computer is fast and can get close to the best team...

See the attached and see if it meets what you are looking for.

"As Expected"

is a pretty big goal to achieve.

I think if you have your data sorted by position so all the C are together and 1B are togethere... That would be the first problem. Then .. Let me look at the code as 140 was the last row for today....

I see that the formulas in columns O to Q would also need updating for it to work with more or less players.....

I'd use a Dynamic Named Range for the Players and Positions to be more generic.
Using the names manager you could create a DNR called
Players = OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)+2,11)

Then use this Name in the formulas in the O:Q cells.

I'd also use a fancy CSE (Control+Shift+Enter) formula for the Max and Min of Start and End Row for player positions. YOU WILL STILL NEED TO SORT BY PLAYER POSITION.

Lastly in this newer version I made sure the 3 outfielders aren't the same person as the first version didn't have that check.

Now, maybe you could do this daily by simply copying in new data...