Update the sorting from high to low in column 2

Question

Update the sorting from high to low in column 2

Chaturvedi, Santosh 340

Hello - The below folmula is working well

=SORT(

FILTER(

    HSTACK(

        '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,

        '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$F$1466:$F$2065*$I$3        ),

    ('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-")*

    (BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0))),

    "N"

),

2,

-1

)

But

i want to sort values from "high to Low" values according to values which are in cloumn 2 (ni(⊥)) .

User's image

Please add and update eh formula above as required.

0 comments

Answer accepted by question author

Jayden-P 2,520 Independent Advisor

Hi @Chaturvedi, Santosh

The original formula is already sorting Column 2 (ni(⊥)) in descending order because the last two arguments are:

2,-1

where 2 means sort by the second column and -1 means highest to lowest.

If you would like to sort by Column 1 (Δσi (⊥) [N/mm²]) instead, you only need to change one part of the formula:

2,-1

to

1,-1

This will sort the results by Column 1 from highest to lowest while keeping the corresponding values in Column 2 aligned with each row.

You can copy this one:

=SORT(
    FILTER(
        HSTACK(
            '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,
            '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$F$1466:$F$2065*$I$3
        ),
        ('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-")*
        (BYROW(
            '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,
            LAMBDA(r,SUM(r)>0)
        )),
        "N"
    ),
    1,
    -1
)

0 comments

2 additional answers

Your answer

Answer 1

Chaturvedi, Santosh 340

I want Δσi (⊥) [N/mm2] column 1 "highest to lowest".

User's image

Please update this formula below

=SORT(

FILTER(

    HSTACK(

        '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,

        '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$F$1466:$F$2065*$I$3        ),

    ('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-")*

    (BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0))),

    "N"

),

2,

-1

)

0 comments

Answer 2

Use the same formula and keep the SORT arguments set to sort by the 2nd column in descending order.

Updated formula:

=SORT(
    FILTER(
        HSTACK(
            '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,
            '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$F$1466:$F$2065*$I$3
        ),
        ('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-")*
        (BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0))),
        "N"
    ),
    2,
    -1
)

2 means Excel sorts by Column 2.

-1 means descending order, which is high to low for numbers.

So the formula already sorts ni(⊥) in Column 2 from highest to lowest.

If the result is not sorting as expected, check that the values in Column 2 are stored as numbers, not text. In Excel, numbers stored as text can prevent correct numeric sorting.

References: