Sort value of contiguous columns in Increasing/Decreasing order

[hsandeep]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	1
4
5
6				AA	CC	BB	DD	EEE	ABC			ABC	BB	EEE	AA	CC	DD
7				44	55	11	77	20	2			2	11	20	44	55	77
8												DD	CC	AA	EEE	BB	ABC
9												77	55	44	20	11	2
Sheet1

 

[hsandeep]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3		1
4
5
6					AA	CC	BB	DD	EEE	ABC			ABC	BB	EEE	AA	CC	DD
7					44	55	11	77	20	2			2	11	20	44	55	77
8
1aa

Cell Formulas
Range	Formula
M6:R6	M6	=INDEX($E$6:$J$6,,MATCH(M7,$E$7:$J$7,0))
M7:R7	M7	=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)),""))

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.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	1
4
5
6				AA	CC	BB	DD	EEE	ABC			ABC	BB	AA	CC	DD	DD
7				44	55	11	77	77	2			2	11	44	55	77	77
Sheet1

Cell Formulas
Range	Formula
M6:R6	M6	=INDEX($E$6:$J$6,,MATCH(M7,$E$7:$J$7,0))
M7:R7	M7	=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]

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]

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

 

[hsandeep]

This formula take dupplicate value in E7:J7 into account.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2
3	2
4
5
6				AA	CC	BB	DD	EEE	ABC			DD	CC	AA	EEE	BB	ABC
7				44	55	11	77	20	2			77	55	44	20	11	2
Sheet1

Cell Formulas
Range	Formula
M6:R7	M6	=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]

This formula take dupplicate value in E7:J7 into account.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2
3	2
4
5
6				AA	CC	BB	DD	EEE	ABC			DD	CC	AA	EEE	BB	ABC
7				44	55	11	77	20	2			77	55	44	20	11	2
Sheet1

Cell Formulas
Range	Formula
M6:R7	M6	=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]

Try:

Book1
	B	C																																								AQ	AR	AS	AT	AU	AV	AW	AX
3	1																																									2	8	11	11	44	55	55	77
4																																										ABC	YY	BB	EEE	AA	CC	XX	DD
5
6
7																																										44	55	11	77	11	2	55	8
8																																										AA	CC	BB	DD	EEE	ABC	XX	YY
Sheet3

Cell Formulas
Range	Formula
AQ3:AX4	AQ3	=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]

Try:

Book1
	B	C																																								AQ	AR	AS	AT	AU	AV	AW	AX
3	1																																									2	8	11	11	44	55	55	77
4																																										ABC	YY	BB	EEE	AA	CC	XX	DD
5
6
7																																										44	55	11	77	11	2	55	8
8																																										AA	CC	BB	DD	EEE	ABC	XX	YY
Sheet3

Cell Formulas
Range	Formula
AQ3:AX4	AQ3	=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]

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]

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

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]

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?

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.