Filling in an array (table) with no Horizontal (row) or Vertical (column) duplicates formulaically i-CodePudding

This may be impossible, but I am trying to create a baseball fielding lineup generator that has a few constraints provided by the league (ie. must play twice in infield, twice in outfield, no repeats at any position). I think this would be fairly simple task using any programming language, but I am designing this for my 70yo uncle and he can basically only use excel with no macros. So I can't brute force my way through the problem and I don't think I understand the mathematics behind the problem well enough to even know if there is a excel formulaic solution.

At it's essence it's a Sudoku creator and solver with no repeats in the rows or columns. I have an ok solution for the infield/outfield part via ranking the individuals by position.

Recursively calculating with F9 is ok for the solution since this is just a change in the options menu, but the last time I sent him a macro his university MS account wouldn't let him run it or change the settings.

Well, I have plenty of other info and have gotten close to solutions using huge nested IFs, but this relatively brute force method seems to be pretty dumb and is not giving great solutions.

Thanks for any help!

CodePudding user response：

Well if your uncle has access to Excel 365 (sounds like a big if), you could use sorting to remove players already used in a given row or column, e.g. like this copied down and across from B2:

=LET(range,$L$2:$L$27,
choice,IF(COUNTIF(B$1:B1,range) COUNTIF($A2:A2,range)=0,range),
count,COUNT(choice),
sort,SORT(choice),
INDEX(sort,RANDBETWEEN(1,count)))

If the players were given names instead of numbers, you could try

=LET(range,$L$2:$L$27,
choice,IF(COUNTIF(B$1:B1,range) COUNTIF($A2:A2,range)=0,range),
count,SUM(--ISTEXT(choice)),
sort,SORT(choice),
INDEX(sort,RANDBETWEEN(1,count)))