Im trying to sort values using a formula.. I started with great confidence but ended up with a headache :)
If A1 = 0, i want the value from the next cell on the same row, less or greater than 0 to show up, and so on. Row by row. Excluding all 0 values and sorting the values less or greater than 0 from first cell on the left.
Is this even possible by only using formulas? I have tried different solutions with IF, INDEX, SORT, FILTER .. but im getting nowhere close to what im trying to do.
First image showing the start values:
And this is what im trying to accomplish using only formulas:
Thankful for all help I can get!
Best regards Jack
Here is one way to accomplish the desired output using one single dynamic array formula:
• Formula used in cell F1
=LET(
a, A1:D4,
IFNA(DROP(REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,
VSTACK(x,SORT(TOROW(INDEX(a,y,)/(INDEX(a,y,)<>0),2))))),1),""))
• Or, If you want to fill down for each row then:
=LET(a, A1:D1, SORT(TOROW(a/(a<>0),2)))
• Or with using FILTER()
function:
=LET(a, A1:D1, SORT(FILTER(a,a<>0,"")))
Or, this way as well:
=LET(
a, A1:D4,
b, SEQUENCE(ROWS(a)),
IFNA(DROP(REDUCE(0,b,LAMBDA(x,y,
LET(z, INDEX(a,y,), VSTACK(x,
SORT(FILTER(z,z<>0)))))),1),""))