# Sort value of contiguous columns in Increasing/Decreasing order



## hsandeep (Dec 29, 2022)

My input range is E6:J7. E7:J7 which generates numerical values *EXCEPT* 0 or “” (null). *ALSO* they are *contiguous columns*. E6:J6 contains headers
IF B3=1, M7:R7 should get values from E7:J7 in *INCREASING* order. *ALSO* M6:R6 headers should be ‘corresponding’.
IF B3=2, M7:R7 should get values from E7:J7 in *DECREASING* order. *ALSO* M6:R6 headers should be ‘corresponding’.

How to achieve this?
Thanks in advance.
Book2.xlsxBCDEFGHIJKLMNOPQR31456AACCBBDDEEEABCABCBBEEEAACCDD744551177202211204455778DDCCAAEEEBBABC977554420112Sheet1


----------



## bebo021999 (Dec 29, 2022)

This formula take dupplicate value in E7:J7 into account.
Book1BCDEFGHIJKLMNOPQR232456AACCBBDDEEEABCDDCCAAEEEBBABC74455117720277554420112Sheet1Cell FormulasRangeFormulaM6:R7M6=INDEX($A6:$J6,AGGREGATE(15,6,COLUMN($E$7:$J$7)/($E$7:$J$7+COLUMN($E$7:$J$7)*10^-2=AGGREGATE(14+IF($B$3=1,1,0),6,$E$7:$J$7+COLUMN($E$7:$J$7)*10^-2,COLUMNS($A:A))),1))


----------



## Dave Patton (Dec 30, 2022)

I used Excel 365.  I will revise if for earlier versions.

Sort.xlsmEFGHIJKLMNOPQR456AACCBBDDEEEABCABCBBEEEAACCDD744551177202211204455778DDCCAAEEEBBABC97755442011210112112044557712771120445577131aCell FormulasRangeFormulaM6:R6,M8:R8N6=XLOOKUP(N7,$E$7:$J$7,$E$6:$J$6)M7:R7M7=SORT(E7:J7,,1,1)M9:R9M9=SORT(E7:J7,1,-1,1)M11:R11,N12:R12M11=SMALL($E$7:$J$7,COLUMNS($M$1:M1))M12M12=LARGE($E$7:$J$7,COLUMNS($M$1:M2))Dynamic array formulas.


----------



## Dave Patton (Dec 30, 2022)

Sort.xlsmEFGHIJKLMNOPQR456AACCBBDDEEEABCABCBBEEEAACCDD744551177202211204455778DDCCAAEEEBBABC977554420112101aaCell FormulasRangeFormulaM6:R6,M8:R8M6=INDEX($E$6:$J$6,,MATCH(M7,$E$7:$J$7,0))M7:R7M7=SMALL($E$7:$J$7,COLUMNS($M$1:M1))M9:R9M9=LARGE($E$7:$J$7,COLUMNS($M$1:M1))


----------



## hsandeep (Dec 30, 2022)

Dave Patton said:


> Sort.xlsmEFGHIJKLMNOPQR456AACCBBDDEEEABCABCBBEEEAACCDD744551177202211204455778DDCCAAEEEBBABC977554420112101aaCell FormulasRangeFormulaM6:R6,M8:R8M6=INDEX($E$6:$J$6,,MATCH(M7,$E$7:$J$7,0))M7:R7M7=SMALL($E$7:$J$7,COLUMNS($M$1:M1))M9:R9M9=LARGE($E$7:$J$7,COLUMNS($M$1:M1))


I tried your formula.  But it is *not populating CORRECTLY*, I am not able to get *AA* & *44* below it!!!  Moreover, '*last column*' populates *#NUM!
Book2.xlsxEFGHIJKLMNOPQR6AACCBBDDEEEABCABCBBEEECCDD#NUM!744551177202211205577#NUM!8DDCCEEEBBABC#NUM!9775520112#NUM!Sheet1Cell FormulasRangeFormulaM8:R8,M6:R6M6=INDEX($E$6:$J$6,,MATCH(M7,$E$7:$J$7,0))M7:R7M7=SMALL($E$7:$J$7,COLUMNS($M$1:M1))M9:R9M9=LARGE($E$7:$J$7,COLUMNS($M$1:M1))*


----------



## Dave Patton (Dec 30, 2022)

The formula works for me.
What part of the formula fails?  Review the formula with Formulas Evaluate Formula.

Are there extraneous characters in your source information?
Try deleting number 44 and then enter 44 again.
Try deleting AA and then enter it again.

Sort.xlsmABCDEFGHIJKLMNOPQR1231456AACCBBDDEEEABCABCBBEEEAACCDD7445511772022112044557781aaCell FormulasRangeFormulaM6:R6M6=INDEX($E$6:$J$6,,MATCH(M7,$E$7:$J$7,0))M7:R7M7=IF($B$3=1,SMALL($E$7:$J$7,COLUMNS($M$1:M1)),IF($B$3=2,LARGE($E$7:$J$7,COLUMNS($M$1:M1)),""))


----------



## hsandeep (Dec 30, 2022)

Dave Patton said:


> The formula works for me.
> What part of the formula fails?  Review the formula with Formulas Evaluate Formula.
> 
> Are there extraneous characters in your source information?
> ...


I was referring to the 3 formulas of yours posted in #4


----------



## Dave Patton (Dec 30, 2022)

*The formula works for me.
What part of the formula fails? Review the formula with Formulas Evaluate Formula.

Are there extraneous characters in your source information?
Try deleting number 44 and then enter 44 again.
Try deleting AA and then enter it again.

Post 6 includes the option from B3
Did you try that formula????*

What is the sum of E7 to J7 ?


----------



## hsandeep (Dec 31, 2022)

Dave Patton said:


> The formula works for me.
> What part of the formula fails?  Review the formula with Formulas Evaluate Formula.
> 
> Are there extraneous characters in your source information?
> ...


I’ll report you the final outcome.

Your formulas of post# 3 *DOES NOT WORKS FOR ME*…may be because I am using *Excel 2010 version*
Your formulas of post# 4 *WORKS*. But it is ‘*independent*’ of B3
Your formulas of post# 6 *WORKS WELL *& meets my requirement. I’ll be using these 2 formulas. Strangely, I don’t find these 2 formulas (of your post# 6) anywhere in your above posts.

Thanks a lot Dave Patton…I feel you really like to help others…May SHYAM BABA bless you.


----------



## hsandeep (Dec 31, 2022)

Dave Patton said:


> The formula works for me.
> What part of the formula fails?  Review the formula with Formulas Evaluate Formula.
> 
> Are there extraneous characters in your source information?
> ...


I am trying to 'replicate' your 2nd formula of post# 6 (as per my data range)

```
=IF($B$3=1,SMALL($E$7:$J$7,COLUMNS($M$1:M1)),IF($B$3=2,LARGE($E$7:$J$7,COLUMNS($M$1:M1)),""))
```

But I am facing problem.
Please *expain*

```
=IF($B$3=1,SMALL($E$7:$J$7,COLUMNS(*$M$1*:*M1*)),IF($B$3=2,LARGE($E$7:$J$7,COLUMNS(*$M$1*:*M1*)),""))
```

 in the formula so that I can replicate it *properly* as my data range


----------



## hsandeep (Dec 29, 2022)

My input range is E6:J7. E7:J7 which generates numerical values *EXCEPT* 0 or “” (null). *ALSO* they are *contiguous columns*. E6:J6 contains headers
IF B3=1, M7:R7 should get values from E7:J7 in *INCREASING* order. *ALSO* M6:R6 headers should be ‘corresponding’.
IF B3=2, M7:R7 should get values from E7:J7 in *DECREASING* order. *ALSO* M6:R6 headers should be ‘corresponding’.

How to achieve this?
Thanks in advance.
Book2.xlsxBCDEFGHIJKLMNOPQR31456AACCBBDDEEEABCABCBBEEEAACCDD744551177202211204455778DDCCAAEEEBBABC977554420112Sheet1


----------



## hsandeep (Jan 3, 2023)

Dave Patton said:


> The formula works for me.
> What part of the formula fails?  Review the formula with Formulas Evaluate Formula.
> 
> Are there extraneous characters in your source information?
> ...


I am using your 2 formulas of post# 6.

```
=INDEX($E$6:$J$6,,MATCH(M7,$E$7:$J$7,0))
```


```
=IF($B$3=1,SMALL($E$7:$J$7,COLUMNS($M$1:M1)),IF($B$3=2,LARGE($E$7:$J$7,COLUMNS($M$1:M1)),""))
```

There is 1 issue which I face while using the above 2 formulas:
IF there is ‘*duplicate value*’ in E7:J7 then the formula for M7:R7 populates *wrong* E7:J7 i.e. if a ‘*duplicate value*’ is as H7=77 & I7=77 with corresponding H6=DD & I6=EEE, I get *wrong* Q6=DD & *R6=DD*. I need R6=EEE in such ‘*duplicate value/(s)*’ *cases*.

Please help Dave Patton
Book2.xlsxBCDEFGHIJKLMNOPQR31456AACCBBDDEEEABCABCBBAACCDDDD74455117777221144557777Sheet1Cell FormulasRangeFormulaM6:R6M6=INDEX($E$6:$J$6,,MATCH(M7,$E$7:$J$7,0))M7:R7M7=IF($B$3=1,SMALL($E$7:$J$7,COLUMNS($M$1:M1)),IF($B$3=2,LARGE($E$7:$J$7,COLUMNS($M$1:M1)),""))


----------



## Dave Patton (Jan 3, 2023)

A solution was provided in post #2 by bebo021999; did you try that suggestion?

I stated in post #3 that I used Excel 365 in that post. Post #4 I showed the components of a solution.
Questions in the later posts were not answered!


----------



## bebo021999 (Jan 4, 2023)

Thanks @Dave Patton 
@hsandeep In early reply #2, it works for dupplicate values. Try it.


----------



## hsandeep (Jan 4, 2023)

bebo021999 said:


> This formula take dupplicate value in E7:J7 into account.
> Book1BCDEFGHIJKLMNOPQR232456AACCBBDDEEEABCDDCCAAEEEBBABC74455117720277554420112Sheet1Cell FormulasRangeFormulaM6:R7M6=INDEX($A6:$J6,AGGREGATE(15,6,COLUMN($E$7:$J$7)/($E$7:$J$7+COLUMN($E$7:$J$7)*10^-2=AGGREGATE(14+IF($B$3=1,1,0),6,$E$7:$J$7+COLUMN($E$7:$J$7)*10^-2,COLUMNS($A:A))),1))


bebo021999
I am trying to use your 2 formulas of post# 2.  Please *confirm back* is it okay?  As I am unable to get the 2nd formula 'clearly' for M7:R7

```
=INDEX($A6:$J6,AGGREGATE(15,6,COLUMN($E$7:$J$7)/($E$7:$J$7+COLUMN($E$7:$J$7)*10^-2=AGGREGATE(14+IF($B$3=1,1,0),6,$E$7:$J$7+COLUMN($E$7:$J$7)*10^-2,COLUMNS($A:A))),1))
```


```
=INDEX($A7:$J7,AGGREGATE(15,6,COLUMN($E$7:$J$7)/($E$7:$J$7+COLUMN($E$7:$J$7)*10^-2=AGGREGATE(14+IF($B$3=1,1,0),6,$E$7:$J$7+COLUMN($E$7:$J$7)*10^-2,COLUMNS($A:A))),1))
```


----------



## hsandeep (Jan 4, 2023)

bebo021999 said:


> This formula take dupplicate value in E7:J7 into account.
> Book1BCDEFGHIJKLMNOPQR232456AACCBBDDEEEABCDDCCAAEEEBBABC74455117720277554420112Sheet1Cell FormulasRangeFormulaM6:R7M6=INDEX($A6:$J6,AGGREGATE(15,6,COLUMN($E$7:$J$7)/($E$7:$J$7+COLUMN($E$7:$J$7)*10^-2=AGGREGATE(14+IF($B$3=1,1,0),6,$E$7:$J$7+COLUMN($E$7:$J$7)*10^-2,COLUMNS($A:A))),1))


bebo021999
My *ACTUAL DATA RANGE* is:

Input *headers*: Instead of E6:J6, it is *AQ8:AX8*
Input *values*: Instead of E7:J7, it is *AQ7:AX7*

Output *headers*: Instead of M6:R6, it is *AQ3:AX3*
Output *values*: Instead of M7:R7, it is *AQ4:AX4*

What *modification* in the 2 formulas is suggested, please, since your both formulas uses A6 and A7.


----------



## bebo021999 (Jan 4, 2023)

Try:

Book1BCAQARASATAUAVAWAX31281111445555774ABCYYBBEEEAACCXXDD567445511771125588AACCBBDDEEEABCXXYYSheet3Cell FormulasRangeFormulaAQ3:AX4AQ3=INDEX($A7:$AX7,AGGREGATE(15,6,COLUMN($AQ$7:$AX$7)/($AQ$7:$AX$7+COLUMN($AQ$7:$AX$7)*10^-2=AGGREGATE(14+IF($B$3=1,1,0),6,$AQ$7:$AX$7+COLUMN($AQ$7:$AX$7)*10^-2,COLUMNS($A:A))),1))


----------



## hsandeep (Saturday at 1:49 AM)

bebo021999 said:


> Try:
> 
> Book1BCAQARASATAUAVAWAX31281111445555774ABCYYBBEEEAACCXXDD567445511771125588AACCBBDDEEEABCXXYYSheet3Cell FormulasRangeFormulaAQ3:AX4AQ3=INDEX($A7:$AX7,AGGREGATE(15,6,COLUMN($AQ$7:$AX$7)/($AQ$7:$AX$7+COLUMN($AQ$7:$AX$7)*10^-2=AGGREGATE(14+IF($B$3=1,1,0),6,$AQ$7:$AX$7+COLUMN($AQ$7:$AX$7)*10^-2,COLUMNS($A:A))),1))


bebo021999
In AQ3 I have started using your formula (*IT WORKS WELL*)

```
=INDEX($A8:$AX8,AGGREGATE(15,6,COLUMN($AQ$7:$AX$7)/($AQ$7:$AX$7+COLUMN($AQ$7:$AX$7)*10^-2=AGGREGATE(14+IF($i$38=1,1,0),6,$AQ$7:$AX$7+COLUMN($AQ$7:$AX$7)*10^-2,COLUMNS($A:A))),1))
```

I would like to know
Why have you used *A8*?
What is the action *10^-2* of in the formula?


----------



## Dave Patton (Saturday at 11:57 AM)

hsandeep  Please keep your questions in the open forum. We volunteer our time to help and to learn.
All readers can learn by trying forum suggestions. We appreciate the common courtesy of answers to our questions when we are trying to help,

Why have you used *A8*?                                        I believe the formula has A7.
What is the action *10^-2* of in the formula?         This evaluates to 0.01 and it is part of the formula to accommodate ties.

check Excel's help for each of the functions
use Excel's Formulas Evaluate Formula to review the formula


----------



## hsandeep (Saturday at 12:27 PM)

Dave Patton said:


> hsandeep  Please keep your questions in the open forum. We volunteer our time to help and to learn.
> All readers can learn by trying forum suggestions. We appreciate the common courtesy of answers to our questions when we are trying to help,
> 
> Why have you used *A8*?                                        I believe the formula has A7.
> ...


Dave
You misunderstood me. I didn't want that you should help me answering above 2 questions. I was asking for my unanswered post, a new post started 2 days before.
Plus I really do not know if the solution has been provided by bebo021999 in this post, then how to ask bebo021999 ONLY...I simply click "Post reply" and asks my questions. It is my fault. Really sorry for the inconvenience caused to you


----------



## bebo021999 (Sunday at 9:03 PM)

hsandeep said:


> bebo021999
> In AQ3 I have started using your formula (*IT WORKS WELL*)
> 
> ```
> ...


There are two ranges: AQ7:AX7 contains numbers and AQ8:AX8 contains corresponding header.
Working with AQ7:AX7 to get ranking and its corresponding position, then its header.
For example, in my sample in #16:
55 is found twice in AQ7:AX7. Both are 2nd ranking. But it required 1st 55 is 2nd then 2nd 55 is 3rd ranking.
Using a tip to get difference: Try to make a very small difference amount between them.
1st 55 is in column(AR) (column index = 44) , 2nd 55 is in column(AW) (column index = 49)
Try to add "column Index *10^-2" into 55 to make them small difference
AR7 = 55 + column()*10^-2  = 55 + column()/100 = 55.44
AW7 = 55 + column()*10^-2  = 55 + column()/100 = 55.49
Using this algorhym, then this array statement:


> $AQ$7:$AX$7+COLUMN($AQ$7:$AX$7)*10^-2


establish an array of values where dupplicate amounts are differrent
={44.43,*55.44*,11.45,77.46,11.47,2.48,*55.49*,8.5} (testing by Highlight that statement in formula bar then hit F9 to see instantly)
Then using AGGREGATE (or small, large) to get the rank.


----------



## hsandeep (Dec 29, 2022)

My input range is E6:J7. E7:J7 which generates numerical values *EXCEPT* 0 or “” (null). *ALSO* they are *contiguous columns*. E6:J6 contains headers
IF B3=1, M7:R7 should get values from E7:J7 in *INCREASING* order. *ALSO* M6:R6 headers should be ‘corresponding’.
IF B3=2, M7:R7 should get values from E7:J7 in *DECREASING* order. *ALSO* M6:R6 headers should be ‘corresponding’.

How to achieve this?
Thanks in advance.
Book2.xlsxBCDEFGHIJKLMNOPQR31456AACCBBDDEEEABCABCBBEEEAACCDD744551177202211204455778DDCCAAEEEBBABC977554420112Sheet1


----------



## hsandeep (Monday at 5:51 AM)

bebo021999 said:


> There are two ranges: AQ7:AX7 contains numbers and AQ8:AX8 contains corresponding header.
> Working with AQ7:AX7 to get ranking and its corresponding position, then its header.
> For example, in my sample in #16:
> 55 is found twice in AQ7:AX7. Both are 2nd ranking. But it required 1st 55 is 2nd then 2nd 55 is 3rd ranking.
> ...


Fantastic & intelligently usage of micro mathematics..I understood use of 10^-2
Lastly why A7 & A8 in the 2 formulas?
If A7 or A8 has a value, will it change the formula's final results?


----------



## bebo021999 (Monday at 8:52 PM)

Its INDEX(range, index) construction
=INDEX($A8:$AX8,index)
If index = column(AQ7) = 43 then
=INDEX($A8:$AX8,43)= AQ8


----------



## hsandeep (Monday at 9:05 PM)

bebo021999 said:


> Its INDEX(range, index) construction
> =INDEX($A8:$AX8,index)
> If index = column(AQ7) = 43 then
> =INDEX($A8:$AX8,43)= AQ8


Understood construction of the formula...it requires in depth vision of the scenarios vis a vis the final outcome that has to be achieved..Many thanks bebo021999


----------

