Forum Discussion
Auto Populate Win Sheet via Numbers in another worksheet
I am running a pool and want to have the Win Sheet auto populate an X in a new column for every time the number comes up in the master worksheet. I was trying to use this formula =IF(COUNTIF(Mast...
If you have access to LAMBDA, you could this:
=MAKEARRAY(100,COUNTIF(numbers,MODE(numbers)),LAMBDA(r,c,IF(c<=COUNTIF(numbers,r),"x","")))
This formula creates a 100x5 matrix. The number of columns can expand depending on how many times the MODE is counted in your numbers list.
This is a fun task. You will get some good options for this one!
Patrick2788 OK because I got called out I had to throw another option out there. lol
=LET(list, $A$1:INDEX($A:$A,COUNT($A:$A)), nn, numbers,
IFERROR(TEXTSPLIT(TEXTJOIN(";",0,REPT("x,",COUNTIF(nn,list))),",",";"),""))so line 1 is just defining the 2 input ranges and all the 'work' is done on line 2
Patrick, I really like your idea of using MODE() to define the # of columns
I also notice you assumed the range of numbers would be the sequence from 1 to X while the above uses the numbers in col A. Another option might be to create the list of used values (inclusive or exclusive of unused values in between used values).