Nov 09 2022 12:14 PM
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(Master!E3:E230,"1"),"X"," ") and it does work, however I am not sure how to make it recognize that the number 1 or any other number between 1-100 could come up more than once in the data set. And to then check the box net to the already checked box in the win sheet.
The Numbers are 1-100 and there are 230 Rows that those numbers could come up in one column.
I have manually marked in the sheet I would like to auto populate for reference.
Sheet I would like to Auto Populate
# | Total | |||||||||||||||||||||||||||||||||||||||||||||
1 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
2 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
3 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
4 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
5 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
6 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
7 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
8 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
9 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
10 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
11 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
12 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
13 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
14 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
15 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
16 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
17 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
18 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
19 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
20 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
21 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
22 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
23 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
24 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
25 | X | X | X | 3 | ||||||||||||||||||||||||||||||||||||||||||
26 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
27 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
28 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
29 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
30 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
31 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
32 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
33 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
34 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
35 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
36 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
37 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
38 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
39 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
40 | X | X | X | X | 4 | |||||||||||||||||||||||||||||||||||||||||
41 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
42 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
43 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
44 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
45 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
46 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
47 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
48 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
49 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
50 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
51 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
52 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
53 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
54 | X | X | X | X | X | 5 | ||||||||||||||||||||||||||||||||||||||||
55 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
56 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
57 | X | X | X | 3 | ||||||||||||||||||||||||||||||||||||||||||
58 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
59 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
60 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
61 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
62 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
63 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
64 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
65 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
66 | X | X | X | 3 | ||||||||||||||||||||||||||||||||||||||||||
67 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
68 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
69 | X | X | X | X | 4 | |||||||||||||||||||||||||||||||||||||||||
70 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
71 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
72 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
73 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
74 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
75 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
76 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
77 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
78 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
79 | X | X | X | X | 4 | |||||||||||||||||||||||||||||||||||||||||
80 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
81 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
82 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
83 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
84 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
85 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
86 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
87 | X | X | X | 3 | ||||||||||||||||||||||||||||||||||||||||||
88 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
89 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
90 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
91 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
92 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
93 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
94 | X | 1 | ||||||||||||||||||||||||||||||||||||||||||||
95 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
96 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
97 | 0 | |||||||||||||||||||||||||||||||||||||||||||||
98 | X | X | X | 3 | ||||||||||||||||||||||||||||||||||||||||||
99 | X | X | 2 | |||||||||||||||||||||||||||||||||||||||||||
100 | X | 1
|
Data Set:
17 |
77 |
17 |
7 |
53 |
26 |
70 |
62 |
83 |
42 |
72 |
88 |
54 |
54 |
40 |
99 |
28 |
29 |
69 |
82 |
69 |
66 |
46 |
48 |
25 |
57 |
57 |
54 |
16 |
47 |
86 |
6 |
27 |
22 |
29 |
58 |
66 |
62 |
83 |
82 |
94 |
71 |
1 |
21 |
24 |
80 |
73 |
41 |
100 |
98 |
98 |
93 |
93 |
98 |
20 |
96 |
87 |
85 |
87 |
99 |
23 |
87 |
92 |
57 |
40 |
40 |
66 |
90 |
25 |
45 |
25 |
96 |
68 |
44 |
48 |
43 |
31 |
39 |
37 |
40 |
75 |
79 |
79 |
79 |
74 |
79 |
69 |
69 |
92 |
1 |
10 |
10 |
54 |
45 |
54 |
44 |
5 |
7 |
27 |
71 |
30 |
16 |
56 |
56 |
46 |
78 |
88 |
85 |
Nov 09 2022 12:41 PM
=IFERROR(IF(ISNUMBER(SMALL(IF($A3=Master!$E$3:$E$110,ROW(Master!$E$3:$E$110)-2),COLUMN(Master!A$1))),"X",""),"")
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula is entered in cell I3 and draged across range I3:M102. In columns B to F are the manually entered expected results.
Nov 09 2022 12:46 PM - edited Nov 09 2022 12:49 PM
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!
Nov 09 2022 02:35 PM
@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).
Nov 09 2022 02:46 PM
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)
)
Nov 09 2022 03:40 PM
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.
Nov 10 2022 06:57 AM
Nov 10 2022 07:02 AM
Nov 10 2022 07:02 AM
Nov 10 2022 09:29 AM
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.
Nov 12 2022 09:37 AM
If you are still having problems, it may be that
= LET(
count, COUNTIFS(Data, numbers),
n, MAX(count),
k, SEQUENCE(1,n),
wins, IF(k<=count,"X",""),
)
would work for you. I have removed the VSTACK that I used simply to lay out the results sheet because the array manipulation functions came out even later than LAMBDA. If I were developing this formula for my own use, I would create a custom Lambda function so that the formula has the same look and feel as every other formula throughout my workbook but that has most users feeling faint and reaching for the smelling salts!
Nov 14 2022 01:55 PM