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



## hsandeep (Dec 29, 2022)

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.xlsxABCDE411254200032004270064210042000426007422004210042500894230042200425001042400423004240011124250042400423001342600425004220014154270042500421001617183200426004200019202142500427003200Pr


----------



## shift-del (Dec 29, 2022)

Hi

Mappe6ABHI4115542000132006421002420007422003421008  942300442200104240054230011  1242500642400134260074250014  154270084250016  17  18320094260019  20  21425001042700Tabelle1Cell FormulasRangeFormulaI4I4=16-A4H5:H21H5=IF(A5="","",COUNT(A$5:A5))I5:I21I5=IFERROR(AGGREGATE($I$4,6,$A$5:$A$21,H5),"")


----------



## hsandeep (Dec 29, 2022)

shift-del said:


> 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 (Dec 30, 2022)

See this explanation.
Mappe6LMNOPQR5functionfunction numbersorting orderyour numberyour sorting ordercommon minuendhow to get the function number from your number6LARGE14descending2descending16147SMALL15ascending1ascending1615Tabelle1Cell FormulasRangeFormulaQ6:Q7Q6=M6+O6R6:R7R6=Q6-O6


----------



## hsandeep (Dec 30, 2022)

shift-del said:


> See this explanation.
> Mappe6LMNOPQR5functionfunction numbersorting orderyour numberyour sorting ordercommon minuendhow to get the function number from your number6LARGE14descending2descending16147SMALL15ascending1ascending1615Tabelle1Cell FormulasRangeFormulaQ6:Q7Q6=M6+O6R6:R7R6=Q6-O6


Thanks shift-del. Many thanks for all your efforts


----------



## shift-del (Dec 30, 2022)

You're welcome, hsandeep.


----------

