1

So, I am working on another issue and need to check a 12 x 6 excel range for errors. If there is an error, I want it to build a new 12 x 6 range within the function and then check that for errors. I am at the very beginning and very new to Lambda Functions in Excel (but I have the basics). I also have a limitation of not using VBA (which I know would be way simpler and cleaner).

So I created a function LoopTest in Name Manager and then in "refers to":

=LAMBDA(X,Y,
IF(Y<=11=TRUE,
 IF(X<=6=TRUE,
  LoopTest(X+1,Y),
  IF(Y=11,
    "TEST SUCCESS",
    LoopTest(0,Y+1)
   )
  )
 )
)

Then =LoopTest(0,0)

This seems to be working correctly (although excel doesn't really allow intermediate testing of the function). So now I assume I can loop through a range with Index(array,X,Y) and check the cells for errors.

The only problem is that I can only do one array/table/range at a time. I need to figure out how to create a test array the first time through and then pass it back each time until the test fails or has complete success (at which point it returns the successful range). I am leaning towards Let() function to define some more variables and hide them behind some IF statements (I haven't used IFS, but have seen others use that to success.) I haven't checked the following formula but the general flow should be correct.

=LAMBDA(X,Y,Array1,
IF(Y<=11=TRUE,
 IF(X<=6=TRUE,
  IF(ISERROR(INDEX(Array1,X,Y))=FALSE,
   LoopTest(X+1,Y,Array1),        'IF True continue checking Array1
   Array1 = NEWARRAY              'IF False I NEED A WAY TO CREATE A NEW ARRAY AND BEGIN CHECKING IT
   IF(Y=11,
    Array1                        'IF True Return the fully checked Array1
    IF(ISERROR(INDEX(Array1,X,Y))=FALSE,
     LoopTest(0,Y+1,Array1)       'IF True continue checking Array1
     Array1 = NEWARRAY            'IF False I NEED A WAY TO CREATE A NEW ARRAY AND BEGIN CHECKING IT
    )
   )
  )
 )
)

The purpose is to allow a range of names with a bunch of qualifications like

Adam
Bill
Camp
Doug
Earl
Fred
Gabe
Hall
Ivan
Kobe
Lane
Mike

And create a range that is unique similar to Sudoku (horizontal and vertical unique).

Gabe    Earl    Fred    Doug    Bill    Ivan
Adam    Gabe    Bill    Lane    Mike    Camp
Mike    Hall    Kobe    Bill    Doug    Gabe
Fred    Doug    Gabe    Camp    Kobe    Mike
Camp    Kobe    Lane    Mike    Ivan    Fred
Bill    Lane    Ivan    Fred    Gabe    Adam
Doug    Camp    Adam    Earl    Hall    Lane
Earl    Adam    Hall    Ivan    Fred    Bill
Lane    Ivan    Mike    Adam    Earl    Hall
Ivan    Mike    Camp    Kobe    Lane    Earl
Hall    Bill    Doug    Gabe    Camp    Kobe
Kobe    Fred    Earl    Hall    Adam    Doug

With 6 positions and 12 names, it will fail more often than succeed (guessing 100 iterations per valid solution), but I want it to keep iterating until the Lambda finds a valid solution. The simple solution of just grabbing names randomly for the table based on what came from above and to the left is about 50/50 on finding a valid solution.

SmithL
  • 23
  • 8
  • 1
    To me it's not clear what you're trying to achieve. An example dataset together with expected result would help. – Jos Woolley Feb 25 '22 at 05:32
  • Thanks for the feedback. I will add additional info. – SmithL Feb 25 '22 at 12:06
  • 1
    Many thanks, but still too generic for me. A sample input and expected output is what I was hoping for. Hopefully others will be able to give you a solution based on what you've written – Jos Woolley Feb 25 '22 at 16:05
  • Not sure if that edit helps. I was trying to bifurcate the formula's to create these ranges and the ability to iterate and sort through them to check if they are valid. They are both a little complicated. – SmithL Feb 25 '22 at 18:47
  • 1
    Thanks. Is it possible then that your entire post could have been more succinctly summarised as: *Given a list of n names, generate an nxp matrix (with p<=n) in which no name occurs more than once in a given column or row*? That said, I appreciate that you've been tackling this problem yourself, and so would rather not make it an 'open' question (instead wanting advice on how to advance the specific approach you've been developing). The only downside to disallowing alternative solutions would seem to be that your current approach is both vague and generic, and so may not garner much attention. – Jos Woolley Feb 25 '22 at 19:55
  • Appreciate the reply. I had another question on how best to fill in the matrix, but I began to discover that Excel could be recursive without VBA by using Lambda. Now I am trying to figure out the particulars of how lambda works so I can implement it in projects that don't allow me to use Macros. I can't find much technical data on the function, so I hoped that this community had used it enough to work out the technical details or had some best practices to share. I appreciate you taking a look. – SmithL Feb 25 '22 at 20:18
  • 1
    I wasn't suggesting that I wouldn't use a `LAMBDA`-based approach. On the contrary! – Jos Woolley Feb 25 '22 at 20:21

0 Answers0