sort an array wrt two columns one ascending and the other descending

[mehmetics]

I am trying to sort and filter function using a formula for multiple columns, using the numeric values in descending order. See example attached.

(Column A) contains a range of text values.
(Column B) contains a range of unsorted numbers.
(Column C) contains a range of unsorted numbers.


Now I would like to find a way of having the text values correspond with the sorted numerical data ( points-1 in descending and points-2 in ascending order) using an array formula. Can anyone help?

[canapone]

Hi,

please see the attached file: there are for sure better solutions.

Not sure formulas in E column are robust

Please Login or Register  to view this content.

in F column

Please Login or Register  to view this content.

in G column (you can use AGGREGATE only with Excel 2010-2013...)

Please Login or Register  to view this content.

Regards

[sktneer]

Hi mehmetics,
Welcome to the Forum.

You can achieve this by using custom sort. To do this follow these step...

Select a cell in the range --> Sort & Filter --> Custom Sort --> First Sort By drop down, select Points 1 --> Sort On (Values) --> Order (Largest to smallest) --> Now Add Level --> Select Points 2 from then by drop down list for new level --> Sort On (Values) --> Order (Smallest to Largest) --> OK.

Does this help.

[canapone]

Hi again (playing on with formulas)

in E2 much better to use

Please Login or Register  to view this content.

Regards

[mehmetics]

Thank you canapone that is what I want.

[mehmetics]

thank you sktneer.
as you said, I can achieve it by using costom sort. But in order to have a dynamic change I wanted to do using an array formula.

[canapone]

Hi,

thanks mehmetics for providing feedback.

Iyi günler