Sort value of contiguous columns in Increasing/Decreasing order

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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.xlsx
BCDEFGHIJKLMNOPQR
31
4
5
6AACCBBDDEEEABCABCBBEEEAACCDD
74455117720221120445577
8DDCCAAEEEBBABC
977554420112
Sheet1
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
This formula take dupplicate value in E7:J7 into account.
Book1
BCDEFGHIJKLMNOPQR
2
32
4
5
6AACCBBDDEEEABCDDCCAAEEEBBABC
74455117720277554420112
Sheet1
Cell Formulas
RangeFormula
M6: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))
 
Upvote 0
I used Excel 365. I will revise if for earlier versions.

Sort.xlsm
EFGHIJKLMNOPQR
4
5
6AACCBBDDEEEABCABCBBEEEAACCDD
74455117720221120445577
8DDCCAAEEEBBABC
977554420112
10
1121120445577
12771120445577
13
1a
Cell Formulas
RangeFormula
M6: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.
 
Upvote 0
Dave Patton said:
Sort.xlsm
EFGHIJKLMNOPQR
4
5
6AACCBBDDEEEABCABCBBEEEAACCDD
74455117720221120445577
8DDCCAAEEEBBABC
977554420112
10
1aa
Cell Formulas
RangeFormula
M6: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))
Click to expand...
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.xlsx
EFGHIJKLMNOPQR
6AACCBBDDEEEABCABCBBEEECCDD#NUM!
744551177202211205577#NUM!
8DDCCEEEBBABC#NUM!
9775520112#NUM!
Sheet1
Cell Formulas
RangeFormula
M8: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))
 
Upvote 0
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.xlsm
ABCDEFGHIJKLMNOPQR
1
2
31
4
5
6AACCBBDDEEEABCABCBBEEEAACCDD
74455117720221120445577
8
1aa
Cell Formulas
RangeFormula
M6: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)),""))
 
Upvote 0
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?
Try deleting number 44 and then enter 44 again.
Try deleting AA and then enter it again.

Sort.xlsm
ABCDEFGHIJKLMNOPQR
1
2
31
4
5
6AACCBBDDEEEABCABCBBEEEAACCDD
74455117720221120445577
8
1aa
Cell Formulas
RangeFormula
M6: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)),""))
Click to expand...
I was referring to the 3 formulas of yours posted in #4
 
Upvote 0
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 ?
 
Upvote 0
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?
Try deleting number 44 and then enter 44 again.
Try deleting AA and then enter it again.

Sort.xlsm
ABCDEFGHIJKLMNOPQR
1
2
31
4
5
6AACCBBDDEEEABCABCBBEEEAACCDD
74455117720221120445577
8
1aa
Cell Formulas
RangeFormula
M6: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)),""))
Click to expand...
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.
 
Upvote 0
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?
Try deleting number 44 and then enter 44 again.
Try deleting AA and then enter it again.

Sort.xlsm
ABCDEFGHIJKLMNOPQR
1
2
31
4
5
6AACCBBDDEEEABCABCBBEEEAACCDD
74455117720221120445577
8
1aa
Cell Formulas
RangeFormula
M6: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)),""))
Click to expand...
I am trying to 'replicate' your 2nd formula of post# 6 (as per my data range)
Rich (BB code): 
=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
Rich (BB code): 
=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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Simplify 2 array formula to calculate risk score
Replies
2
Views
98
djbe17
D
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs
V
  • Solved
Average Function
Replies
8
Views
395
Peter_SSs
Peter_SSs
H
  • Solved
Sort 4 numbers in increasing & decreasing order
Replies
4
Views
405
Fluff
Fluff
H
  • Solved
Increasing & Decreasing order of a range of integers
2
Replies
11
Views
650
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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