Forum Discussion
Auto Populate Win Sheet via Numbers in another worksheet
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!
- Thanks Patrick - I do not have access to Lambda, However I will look into this further - may have it through a work account and not know.
- A lot of companies use the Semi-Annual update channel where these functions aren't available, but you may have access to them in the Excel web app.
A simple dynamic array solution will do here. Lambda and helpers get you into the Microsoft nested arrays error (their error I contend, not ours).
= LET( count, COUNTIFS(Data, numbers), n, MAX(count), k, SEQUENCE(1,n), wins, IF(k<=count,"X",""), VSTACK(k,wins) )I seldom use MAKEARRAY, but this request seemed like a good opportunity to put it to use. If timed to the millisecond, it's undoubtedly the slowest of the solutions. EXPAND is another one but there's only so much one can do with that one.
I agree, a whole new toolbox has been provided and there is every reason to build up experience of the use of new tools. Some problems will have natural solutions that form a 2D array of single-cell results. Others really do require Microsoft to sort the 'nested arrays' error. There are problems whereby I have been reduced to performing a computationally intensive calculation to return a 1D array, select one term, then recalculate the same array.
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).
