excelsortingfilterformula

Sort values and remove "0" with formula


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:

Values

And this is what im trying to accomplish using only formulas:

What im trying to accomplish

Thankful for all help I can get!

Best regards Jack


Solution

  • Here is one way to accomplish the desired output using one single dynamic array formula:

    enter image description here


    • 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),""))