Increasing & Decreasing order of a range of integers

[hsandeep]

Input Range 1: C3:C17 is contiguous rows which generates positive integer values AND they are unique to each other AND >0
Input Range 2: E3:E17 is contiguous rows which generates positive integer values AND they are unique to each other AND >0

Output range 3: G3:G17: Values from Range 1 in INCREASING order of Range 2
Output range 4: I3:I17: Values from Range 1 in DECREASING order of Range 2

How to accomplish? Thanks in advance.

Referencing.xlsx
	C	D	E	F	G	H	I
2					Increasing		Decreasing
3	50		999		64		50
4	44		888		60		44
5	62		444		2		99
6	88		777		3		88
7	22		220		45		62
8	11		150		87		22
9	99		800		10		1
10	45		65		11		11
11	87		80		1		10
12	64		1		22		87
13	60		8		62		45
14	10		90		88		3
15	1		156		99		2
16	2		10		44		60
17	3		20		50		64
Sheet3

 

[lrobbo314]

How about this?

hsandeep
	A	B	C	D	E	F	G	H	I	J	K
1	Input 1	Input 2		Increasing	Decreasing		365 Inc	365 Dec		<365 Dec	<365 Inc
2	50	999		64	50		64	50		64	50
3	44	888		60	44		60	44		60	44
4	62	444		2	99		2	99		2	99
5	88	777		3	88		3	88		3	88
6	22	220		45	62		45	62		45	62
7	11	150		87	22		87	22		87	22
8	99	800		10	1		10	1		10	1
9	45	65		11	11		11	11		11	11
10	87	80		1	10		1	10		1	10
11	64	1		22	87		22	87		22	87
12	60	8		62	45		62	45		62	45
13	10	90		88	3		88	3		88	3
14	1	156		99	2		99	2		99	2
15	2	10		44	60		44	60		44	60
16	3	20		50	64		50	64		50	64
Sheet1

Cell Formulas
Range	Formula
G2:G16	G2	=SORTBY(A2:A16,B2:B16)
H2:H16	H2	=SORTBY(A2:A16,B2:B16,-1)
J2:J16	J2	=INDEX($A$2:$A$16,MATCH(SMALL($B$2:$B$16,ROWS($A$1:A1)),$B$2:$B$16,0))
K2:K16	K2	=INDEX($A$2:$A$16,MATCH(LARGE($B$2:$B$16,ROWS($A$1:A1)),$B$2:$B$16,0))
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	C	D	E	F	G	H	I
1
2					Increasing		Decreasing
3	50		999		64		50
4	44		888		60		44
5	62		444		2		99
6	88		777		3		88
7	22		220		45		62
8	11		150		87		22
9	99		800		10		1
10	45		65		11		11
11	87		80		1		10
12	64		1		22		87
13	60		8		62		45
14	10		90		88		3
15	1		156		99		2
16	2		10		44		60
17	3		20		50		64
Main

Cell Formulas
Range	Formula
G3:G17	G3	=INDEX($C$3:$C$17,MATCH(SMALL($E$3:$E$17,ROWS(G$3:G3)),$E$3:$E$17,0))
I3:I17	I3	=INDEX($C$3:$C$17,MATCH(LARGE($E$3:$E$17,ROWS(I$3:I3)),$E$3:$E$17,0))

 

[hsandeep]

How about

Fluff.xlsm
	C	D	E	F	G	H	I
1
2					Increasing		Decreasing
3	50		999		64		50
4	44		888		60		44
5	62		444		2		99
6	88		777		3		88
7	22		220		45		62
8	11		150		87		22
9	99		800		10		1
10	45		65		11		11
11	87		80		1		10
12	64		1		22		87
13	60		8		62		45
14	10		90		88		3
15	1		156		99		2
16	2		10		44		60
17	3		20		50		64
Main

Cell Formulas
Range	Formula
G3:G17	G3	=INDEX($C$3:$C$17,MATCH(SMALL($E$3:$E$17,ROWS(G$3:G3)),$E$3:$E$17,0))
I3:I17	I3	=INDEX($C$3:$C$17,MATCH(LARGE($E$3:$E$17,ROWS(I$3:I3)),$E$3:$E$17,0))

@Fluff
As always Lightning Fast...it works & meets my expectations. Many thanks Fluff

 

[hsandeep]

How about this?

hsandeep
	A	B	C	D	E	F	G	H	I	J	K
1	Input 1	Input 2		Increasing	Decreasing		365 Inc	365 Dec		<365 Dec	<365 Inc
2	50	999		64	50		64	50		64	50
3	44	888		60	44		60	44		60	44
4	62	444		2	99		2	99		2	99
5	88	777		3	88		3	88		3	88
6	22	220		45	62		45	62		45	62
7	11	150		87	22		87	22		87	22
8	99	800		10	1		10	1		10	1
9	45	65		11	11		11	11		11	11
10	87	80		1	10		1	10		1	10
11	64	1		22	87		22	87		22	87
12	60	8		62	45		62	45		62	45
13	10	90		88	3		88	3		88	3
14	1	156		99	2		99	2		99	2
15	2	10		44	60		44	60		44	60
16	3	20		50	64		50	64		50	64
Sheet1

Cell Formulas
Range	Formula
G2:G16	G2	=SORTBY(A2:A16,B2:B16)
H2:H16	H2	=SORTBY(A2:A16,B2:B16,-1)
J2:J16	J2	=INDEX($A$2:$A$16,MATCH(SMALL($B$2:$B$16,ROWS($A$1:A1)),$B$2:$B$16,0))
K2:K16	K2	=INDEX($A$2:$A$16,MATCH(LARGE($B$2:$B$16,ROWS($A$1:A1)),$B$2:$B$16,0))
Dynamic array formulas.

@lrobbo314
Splendid. Thanks Irobbo314. Your formulas works well

 

[hsandeep]

How about

Fluff.xlsm
	C	D	E	F	G	H	I
1
2					Increasing		Decreasing
3	50		999		64		50
4	44		888		60		44
5	62		444		2		99
6	88		777		3		88
7	22		220		45		62
8	11		150		87		22
9	99		800		10		1
10	45		65		11		11
11	87		80		1		10
12	64		1		22		87
13	60		8		62		45
14	10		90		88		3
15	1		156		99		2
16	2		10		44		60
17	3		20		50		64
Main

Cell Formulas
Range	Formula
G3:G17	G3	=INDEX($C$3:$C$17,MATCH(SMALL($E$3:$E$17,ROWS(G$3:G3)),$E$3:$E$17,0))
I3:I17	I3	=INDEX($C$3:$C$17,MATCH(LARGE($E$3:$E$17,ROWS(I$3:I3)),$E$3:$E$17,0))

BTW Fluff in K3:K5 can I get the Lowest, 2nd lowest & 3rd lowest of G3:G17 respectively
Similarly in M3:M5, I need Largest, 2nd largest & 3rd largest of I3:I17 respectively

 

[Fluff]

As always Lightning Fast...it works & meets my expectations. Many thanks Fluff

You're welcome & thanks for the feedback.

 

[Fluff]

BTW Fluff in K3:K5 can I get the Lowest, 2nd lowest & 3rd lowest of G3:G17 respectively
Similarly in M3:M5, I need Largest, 2nd largest & 3rd largest of I3:I17 respectively

Do you just mean the 1st three answer from those columns?

 

[hsandeep]

Do you just mean the 1st three answer from those columns?

No. From 'whole G' lowest, 2nd & 3rd. Similarly from 'whole I'..

 

[Fluff]

How about in K3

=SMALL(C3:C17,ROWS(K$3:K3))

and just change small to large for M3