Visual Representing N Choose R in Sheets

Question

I am trying to create a visual representation of all combinations (N Choose R) from a given list. I want to be able to change the value of R and also be able to change N by adding or removing items from the list as needed. So the output should be a dynamic array that changes based on the list and the choice of R. Here is my sample sheet where I will have values in Column A, the selector for R in Column C, and looking for the results to generate anywhere after E: (https://docs.google.com/spreadsheets/d/1tMq79omHTxAE1-Gh3lr7IIdTs2TjdRR0twWhFv6xEw0/edit#gid=0)

If it is easier to do in Google Apps Script then in Sheets alone, I am happy to see that solution, either way. I know the bare bone basics of Google Apps Scripting. Thanks!

I am truly stumped. I don't have the skills to do this but am hoping if provided a solution I can learn from it. I have tried to have AI generate/teach me a solution and so far no luck in Google Sheets.

Answer 1

Here's a possible solution:

=ARRAYFORMULA(
   LET(list,A3:A8,
       r,C3,
       IF(r=ROWS(list),JOIN(", ",list),
         LET(list_,TOROW(list&"∈"&COUNTIFS(list,list,SEQUENCE(ROWS(list)),"<="&SEQUENCE(ROWS(list)))),
             all,SPLIT(REDUCE(,SEQUENCE(r),LAMBDA(a,i,TOCOL(a&"ζ"&list_))),"ζ"),
             REGEXREPLACE(
               UNIQUE(
                 BYROW(
                   FILTER(all,BYROW(all,LAMBDA(row,AND(COUNTIF(row,list_)<=COUNTIF(list_,list_))))),
                   LAMBDA(row,JOIN(", ",TOROW(SORT(TOCOL(row))))))),
               "∈\d+",)))))

This formula generates all possible combinations of r values and then it filters out the ones with repetitions.