Increasing & Decreasing order of a range of integers

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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
CDEFGHI
2IncreasingDecreasing
3509996450
4448886044
562444299
688777388
7222204562
8111508722
999800101
1045651111
118780110
126412287
136086245
141090883
151156992
162104460
173205064
Sheet3
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
How about
Fluff.xlsm
CDEFGHI
1
2IncreasingDecreasing
3509996450
4448886044
562444299
688777388
7222204562
8111508722
999800101
1045651111
118780110
126412287
136086245
141090883
151156992
162104460
173205064
Main
Cell Formulas
RangeFormula
G3:G17G3=INDEX($C$3:$C$17,MATCH(SMALL($E$3:$E$17,ROWS(G$3:G3)),$E$3:$E$17,0))
I3:I17I3=INDEX($C$3:$C$17,MATCH(LARGE($E$3:$E$17,ROWS(I$3:I3)),$E$3:$E$17,0))
 
Upvote 0
Solution
How about
Fluff.xlsm
CDEFGHI
1
2IncreasingDecreasing
3509996450
4448886044
562444299
688777388
7222204562
8111508722
999800101
1045651111
118780110
126412287
136086245
141090883
151156992
162104460
173205064
Main
Cell Formulas
RangeFormula
G3:G17G3=INDEX($C$3:$C$17,MATCH(SMALL($E$3:$E$17,ROWS(G$3:G3)),$E$3:$E$17,0))
I3:I17I3=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
 
Upvote 0
How about this?

hsandeep
ABCDEFGHIJK
1Input 1Input 2IncreasingDecreasing365 Inc365 Dec<365 Dec<365 Inc
250999645064506450
344888604460446044
462444299299299
588777388388388
622220456245624562
711150872287228722
899800101101101
94565111111111111
108780110110110
11641228722872287
12608624562456245
131090883883883
141156992992992
15210446044604460
16320506450645064
Sheet1
Cell Formulas
RangeFormula
G2:G16G2=SORTBY(A2:A16,B2:B16)
H2:H16H2=SORTBY(A2:A16,B2:B16,-1)
J2:J16J2=INDEX($A$2:$A$16,MATCH(SMALL($B$2:$B$16,ROWS($A$1:A1)),$B$2:$B$16,0))
K2:K16K2=INDEX($A$2:$A$16,MATCH(LARGE($B$2:$B$16,ROWS($A$1:A1)),$B$2:$B$16,0))
Dynamic array formulas.
 
Upvote 1
How about this?

hsandeep
ABCDEFGHIJK
1Input 1Input 2IncreasingDecreasing365 Inc365 Dec<365 Dec<365 Inc
250999645064506450
344888604460446044
462444299299299
588777388388388
622220456245624562
711150872287228722
899800101101101
94565111111111111
108780110110110
11641228722872287
12608624562456245
131090883883883
141156992992992
15210446044604460
16320506450645064
Sheet1
Cell Formulas
RangeFormula
G2:G16G2=SORTBY(A2:A16,B2:B16)
H2:H16H2=SORTBY(A2:A16,B2:B16,-1)
J2:J16J2=INDEX($A$2:$A$16,MATCH(SMALL($B$2:$B$16,ROWS($A$1:A1)),$B$2:$B$16,0))
K2:K16K2=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
 
Upvote 0
How about
Fluff.xlsm
CDEFGHI
1
2IncreasingDecreasing
3509996450
4448886044
562444299
688777388
7222204562
8111508722
999800101
1045651111
118780110
126412287
136086245
141090883
151156992
162104460
173205064
Main
Cell Formulas
RangeFormula
G3:G17G3=INDEX($C$3:$C$17,MATCH(SMALL($E$3:$E$17,ROWS(G$3:G3)),$E$3:$E$17,0))
I3:I17I3=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
 
Upvote 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
Do you just mean the 1st three answer from those columns?
 
Upvote 0
How about in K3
Excel Formula:
=SMALL(C3:C17,ROWS(K$3:K3))
and just change small to large for M3
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top