given the sequence of numbers:

1
200
40
6000
117000
112000
34
90
180020
0
1320
24975
84
116
186
224
315070
10800
333333
266000

how would one remove all red cells:

the only rule is that every number that is lower than the max value of a range above it needs to be removed from the array

so far I got this with:

=ARRAYFORMULA(UNIQUE(QUERY(SPLIT(FLATTEN(ROW(A:A)&"×"&
 IF(ROW(A:A)>=TRANSPOSE(ROW(A:A)), TRANSPOSE(A:A), )), "×"), 
 "select max(Col2) group by Col1 label max(Col2)''")))

but I don't like it coz it gets slow with a lot of more numbers

CodePudding user response：

Assuming your original values are in A2:A ...

=FILTER(A2:A,A2:A<>"",COUNTIFS(A2:A,">"&A2:A,ROW(A2:A),"<="&ROW(A2:A))=0)

In plain English: "Return only non-null values from the target range where there are no higher values in rows up to the current row."