Sort value on non-contiguous rows in increasing/Decreasing order

[hsandeep]

My input range is A5:A21 which generates numerical values EXCEPT 0 or “” (null). ALSO they are non-contiguous rows.
Output range is C5:C21
IF A4=1, the values of C5:C21 should be in INCREASING order
IF A4=2, the values of C5:C21 should be in DECREASING order

How to achieve this?
Thanks in advance.

Book2.xlsx
	A	B	C	D	E
4	1		1		2
5	42000		3200		42700
6	42100		42000		42600
7	42200		42100		42500
8
9	42300		42200		42500
10	42400		42300		42400
11
12	42500		42400		42300
13	42600		42500		42200
14
15	42700		42500		42100
16
17
18	3200		42600		42000
19
20
21	42500		42700		3200
Pr

 

[shift-del]

Hi

Mappe6
	A	B						H	I
4	1								15
5	42000							1	3200
6	42100							2	42000
7	42200							3	42100
8
9	42300							4	42200
10	42400							5	42300
11
12	42500							6	42400
13	42600							7	42500
14
15	42700							8	42500
16
17
18	3200							9	42600
19
20
21	42500							10	42700
Tabelle1

Cell Formulas
Range	Formula
I4	I4	=16-A4
H5:H21	H5	=IF(A5="","",COUNT(A$5:A5))
I5:I21	I5	=IFERROR(AGGREGATE($I$4,6,$A$5:$A$21,H5),"")

 

[hsandeep]

Hi

A

Amazing..liked the concept of helper column. Your formula works well shift-del, thanks for it
Please tell me In I5, why 16 is being used or rather how did you arrived at the figure of 16?

 

[shift-del]

See this explanation.

Mappe6
	L	M	N	O	P	Q	R
5	function	function number	sorting order	your number	your sorting order	common minuend	how to get the function number from your number
6	LARGE	14	descending	2	descending	16	14
7	SMALL	15	ascending	1	ascending	16	15
Tabelle1

Cell Formulas
Range	Formula
Q6:Q7	Q6	=M6+O6
R6:R7	R6	=Q6-O6

 

[hsandeep]

See this explanation.

Mappe6
	L	M	N	O	P	Q	R
5	function	function number	sorting order	your number	your sorting order	common minuend	how to get the function number from your number
6	LARGE	14	descending	2	descending	16	14
7	SMALL	15	ascending	1	ascending	16	15
Tabelle1

Cell Formulas
Range	Formula
Q6:Q7	Q6	=M6+O6
R6:R7	R6	=Q6-O6

Thanks shift-del. Many thanks for all your efforts

 

[shift-del]

You're welcome, hsandeep.