How to compare excel dynamic array elements against entire dynamic array

Question

So assume the following array in excel {1,3,10,8,5,7,9} What I want to do is compare each row to the rest of the array and test which elements are less than or equal to that element and return an array of the elements.

so desired output is something like:

elements	result
1	1
3	3,1
10	10,9,7,8,5,3,1
8	8,7,5,3,1
5	5,3,1
7	7,8,5,3,1
9	9,7,8,5,3,1

Sort order of the elements of the result is unimportant so much as achieving this result. Is there any advice someone can offer on how to do this for dynamic array?

Answer 1

This could also be a solution:

=LET(a,{10;3;1;8;5;7;9},
HSTACK(a,
       MAP(a, LAMBDA(b,
       ARRAYTOTEXT(TOROW(a/(a<=b),2))))))

This first declares the (vertical) array,

Than we stack this and the mapped array's result of each individual value in comparison to the whole array, where TOROW(a/(a<=b),2) produces and filters out errors for any values greater than the individual value and ARRAYTOTEXT joins the result for each iteration delimited by comma.

Or if you'd want to refer to a range holding the numbers instead of declaring the array inside LET, you could use a shorter version of the same:

=LET(a,A2:A8,MAP(a,LAMBDA(b,ARRAYTOTEXT(TOROW(a/(a<=b),2)))))

Or for previous Excel versions use this in B2 (entered with `ctrl+shift+enter') and drag down:

=TEXTJOIN(", ",1,IF($A$2:$A$8<=A2,$A$2:$A$8,""))

Answer 2

Assuming there is no Excel Constraints then one could use the following to reach at the desired output:

---

• Formula used in cell B2

=LET(
     α, A2#,
     φ, TOROW(α),
     BYROW(IF((α<=φ)*ISERR(FIND("-"&α&"-","-"&φ&"-")),α,φ),
     LAMBDA(x,TEXTJOIN(", ",1,UNIQUE(SORT(x,,-1,1),1)))))

---

Answer 3

What about this:

=LET(in,A2:A8, BYROW(in, LAMBDA(x, TEXTJOIN(",",,FILTER(in, in <= x)))))

Assuming there are no Excel version constraints as per the tags listed in the question. Put the previous formula in B2 and this is the output:

It just iterates over all input elements via BYROW and on each iteration, filters by the values that are less or equal than x and returns the result on each row as a text delimited by a comma via TEXTJOIN.