Reorder Rows according to another cells value

[gtd526]

Hello,
I'd like to reorder rows (A3:A17) containing the values in (H21:H35) in the same order as (H21:H35). Thank you.
Here is the data:

NFL.xlsm
	A	B	C	D	E	F	G	H	I
3	IF(AND($A4=$AY4,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5,$AD4>$AD5,$AE4>$AE5,$AF4>$AF5,$AG4<$AG5,$AH4>$AH5,$AI4>$AI5,$AJ4>$AJ5,$AK4>$AK5,$AL4>$AL5,$AM4>$AM5,$AN4>$AN5),"AA:AN (SRS/Pass/Pts)"	IF(AND($A5=$AY5,$AA5>$AA4,$AB5>$AB4,$AC5>$AC4,$AD5>$AD4,$AE5>$AE4,$AF5>$AF4,$AG5<$AG4,$AH5>$AH4,$AI5>$AI4,$AJ5>$AJ4,$AK5>$AK4,$AL5>$AL4,$AM5>$AM4,$AN5>$AN4),"AA:AN (SRS/Pass/Pts)"
4	,IF(AND($A4=$AY4,$AF4>$AF5,$AG4<$AG5,$AH4>$AH5,$AK4>$AK5,$AM4>$AM5,$AO4>$AO5),"AF:AH,AK,AM,AO (*SRS/*Pass/*Pts)"	,IF(AND($A5=$AY5,$AF5>$AF4,$AG5<$AG4,$AH5>$AH4,$AK5>$AK4,$AM5>$AM4,$AO5>$AO4),"AF:AH,AK,AM,AO (*SRS/*Pass/*Pts)"
5	,IF(AND($A4=$AY4,$R4>$R5,$T4>$T5,$AA4>$AA5,$AJ4<$AJ5,$AM4>$AM5),"R,T,AA,AJ,AM (R/R/SRS/NY/A/Sc%)"	,IF(AND($A5=$AY5,$R5>$R4,$T5>$T4,$AA5>$AA4,$AJ5>$AJ4,$AM5>$AM4),"R,T,AA,AJ,AM (R/R/SRS/NY/A/Sc%)"
6	,IF(AND($A4=$AY4,$AD4>$AD5,$AE4>$AE5,$AF4>$AF5,$AG4<$AG5,$AH4>$AH5,$AI4>$AI5,$AJ4>$AJ5,$AK4>$AK5,$AL4>$AL5,$AM4>$AM5,$AN4>$AN5),"AD:AN (Pass/Pts)"	,IF(AND($A5=$AY5,$AD5>$AD4,$AE5>$AE4,$AF5>$AF4,$AG5<$AG4,$AH5>$AH4,$AI5>$AI4,$AJ5>$AJ4,$AK5>$AK4,$AL5>$AL4,$AM5>$AM4,$AN5>$AN4),"AD:AN (Pass/Pts)"
7	,IF(AND($A4=$AY4,$V4>$V5,$AJ4>$AJ5,$AM4>$AM5),"V,AJ,AM (Cov%/Sc%/NY/A)"	,IF(AND($A5=$AY5,$V5>$V4,$AJ5>$AJ4,$AM5>$AM4),"V,AJ,AM (Cov%/Sc%/NY/A)"
8	,IF(AND($A4=$AY4,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5,$AK4>$AK5,$AL4>$AL5,$AM4>$AM5,$AN4>$AN5),"AA:AC,AK:AN (SRS/Pts)"	,IF(AND($A5=$AY5,$AA5>$AA4,$AB5>$AB4,$AC5>$AC4,$AK5>$AK4,$AL5>$AL4,$AM5>$AM4,$AN5>$AN4),"AA:AC,AK:AN (SRS/Pts)"
9	,IF(AND($A4=$AY4,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5,$AD4>$AD5,$AE4>$AE5,$AF4>$AF5,$AG4<$AG5,$AH4>$AH5,$AI4>$AI5,$AJ4>$AJ5),"AA:AJ (SRS/Pass)"	,IF(AND($A5=$AY5,$AA5>$AA4,$AB5>$AB4,$AC5>$AC4,$AD5>$AD4,$AE5>$AE4,$AF5>$AF4,$AG5<$AG4,$AH5>$AH4,$AI5>$AI4,$AJ5>$AJ4),"AA:AJ (SRS/Pass)"
10	,IF(AND($A4=$AY4,$R4>$R5,$T4<$T5,$V4>$V5,$W4>$W5,$Y4>$Y5),"R,T,V,W,Y (R/R/ATS)"	,IF(AND($A5=$AY5,$R5>$R4,$T5<$T4,$V5>$V4,$W5>$W4,$Y5>$Y4),"R,T,V,W,Y (R/R/ATS)"
11	,IF(AND($A4=$AY4,$AD4>$AD5,$AE4>$AE5,$AF4>$AF5,$AG4<$AG5,$AH4>$AH5,$AI4>$AI5,$AJ4>$AJ5),"AD:AJ (Passing)"	,IF(AND($A5=$AY5,$AD5>$AD4,$AE5>$AE4,$AF5>$AF4,$AG5<$AG4,$AH5>$AH4,$AI5>$AI4,$AJ5>$AJ4),"AD:AJ (Passing)"
12	,IF(AND($A4=$AY4,$AK4>$AK5,$AL4>$AL5,$AM4>$AM5,$AN4>$AN5),"AK:AN (PTS)"	,IF(AND($A5=$AY5,$AK5>$AK4,$AL5>$AL4,$AM5>$AM4,$AN5>$AN4),"AK:AN (PTS)"
13	,IF(AND($A4=$AY4,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5),"AA:AC (SRS)"	,IF(AND($A5=$AY5,$AA5>$AA4,$AB5>$AB4,$AC5>$AC4),"AA:AC (SRS)"
14	,IF(AND($A4=$AY4,$AJ4=$AJ5,$AM4>$AM5),"AJ,AM (NY/A/Sc%)"	,IF(AND($A5=$AY5,$AJ5>$AJ4,$AM5>$AM4),"AJ,AM (NY/A/Sc%)"
15	,IF(AND($A4=$AY4,$AB4>$AB5,$AD4>$AD5,$AF4>$AF5,$AH4>$AH5,$AJ4>$AJ5,$AM4>$AM5),"AB,AD,AF,AH,AJ,AM (Misc)"	,IF(AND($A5=$AY5,$AB5>$AB5,$AD5>$AD5,$AF5>$AF5,$AH5>$AH5,$AJ5>$AJ5,$AM5>$AM5),"AB,AD,AF,AH,AJ,AM (Misc)"
16	,IF(AND($A4=$AY4,$V4>$V5,$W4>$W5,$Y4>$Y5),"V:W,Y (ATS)"	,IF(AND($A5=$AY5,$V5>$V4,$W5>$W4,$Y5>$Y4),"V:W,Y (ATS)"
17	,IF(AND($A4=$AY4,$S4<$S5,$U4<$U5),"S,U (R/R)","")))))))))))))))	,IF(AND($A5=$AY5,$S5<$S4,$U5<$U4),"S,U (R/R)","")))))))))))))))
18
19							Current
20	Order of Indicator List	How many Indicators	vs Criteria	vs Wager	Criteria %	Rank (No Dupe Ranks)	Sorted by LARGE	Top Criteria	Top Rank
21	AA:AN (SRS/Pass/Pts)	1	TRUE	TRUE	42.9%	6	52.3%	S,U (R/R)	1
22	AF:AH,AK,AM,AO (*SRS/*Pass/*Pts)	2	TRUE	TRUE	18.8%	13	50.0%	R,T,V,W,Y (R/R/ATS)	2
23	AD:AN (Pass/Pts)	3	TRUE	TRUE	19.2%	12	50.0%	AA:AC (SRS)	3
24	V,AJ,AM (Cov%/Sc%/NY/A)	4	TRUE	TRUE	38.2%	9	49.0%	AK:AN (PTS)	4
25	AA:AC,AK:AN (SRS/Pts)	5	TRUE	TRUE	43.8%	5	43.8%	AA:AC,AK:AN (SRS/Pts)	5
26	AA:AJ (SRS/Pass)	6	TRUE	TRUE	40.0%	7	42.9%	AA:AN (SRS/Pass/Pts)	6
27	R,T,V,W,Y (R/R/ATS)	7	TRUE	TRUE	50.0%	2	40.0%	AA:AJ (SRS/Pass)	7
28	AD:AJ (Passing)	8	TRUE	TRUE	36.1%	10	40.0%	AB,AD,AF,AH,AJ,AM (Misc)	8
29	AK:AN (PTS)	9	TRUE	TRUE	49.0%	4	38.2%	V,AJ,AM (Cov%/Sc%/NY/A)	9
30	AA:AC (SRS)	10	TRUE	TRUE	50.0%	3	36.1%	AD:AJ (Passing)	10
31	R,T,AA,AJ,AM (R/R/SRS/NY/A/Sc%)	11	TRUE	TRUE		100	35.6%	V:W,Y (ATS)	11
32	AJ,AM (NY/A/Sc%)	12	TRUE	TRUE		101	19.2%	AD:AN (Pass/Pts)	12
33	AB,AD,AF,AH,AJ,AM (Misc)	13	TRUE	TRUE	40.0%	8	18.8%	AF:AH,AK,AM,AO (*SRS/*Pass/*Pts)	13
34	V:W,Y (ATS)	14	TRUE	TRUE	35.6%	11	#NUM!	R,T,AA,AJ,AM (R/R/SRS/NY/A/Sc%)	100
35	S,U (R/R)	15	TRUE	TRUE	52.3%	1	#NUM!	AJ,AM (NY/A/Sc%)	101
Test

Cell Formulas
Range	Formula
C21:C35	C21	=COUNTIF('Weekly Picks'!$BH$4:$BP$76,$A21)>0
D21:D35	D21	=COUNTIF([Wagers.xlsm]NFL!$R$1:$BF$1,$A21)>0
E21:E35	E21	=INDEX('Criteria Ranks'!$C$3:$C$20,MATCH(Indicators!$A30,'Criteria Ranks'!$A$3:$A$20,0))
F33:F35,F21:F30	F21	=RANK.EQ($E21,$E$21:$E$35)+COUNTIF($E$21:$E21,$E21)-1
G21:G35	G21	=LARGE($E$21:$E$35, ROWS(E$21:E21))
H21:H35	H21	=INDEX($A$21:$A$35,MATCH($I21,$F$21:$F$35,0))
I21:I35	I21	=SMALL($F$21:$F$35, ROWS(I$21:I21))
A32:A35,A21:A22	A21	=MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
A23:A30	A23	=MID($A6,FIND("""",$A6)+1,FIND("""",$A6,FIND("""",$A6)+1)-FIND("""",$A6)-1)
A31	A31	=MID($A5,FIND("""",$A5)+1,FIND("""",$A5,FIND("""",$A5)+1)-FIND("""",$A5)-1)

 

[jasonb75]

It might be easier for us to follow if you fix your example. A3:B17 appear to contain corrupt and invalid formulas.

In theory, it should be as simple as entering =H21 into A3 then filling down as needed, in reality nothing is ever as simple as it should be.

 

[gtd526]

It might be easier for us to follow if you fix your example. A3:B17 appear to contain corrupt and invalid formulas.

In theory, it should be as simple as entering =H21 into A3 then filling down as needed, in reality nothing is ever as simple as it should be.

The formulas in A3:B17 are copied to another worksheet. Please ignore the formulas, they don't apply to the shown worksheet.
I want to re-arrange the order A3:A17 (rows) if 'contains' the value in H21:H35. Resulting in the same order of H21:H35.

 

[jasonb75]

The formulas in A3:B17 are copied to another worksheet. Please ignore the formulas, they don't apply to the shown worksheet.

If you want to re-order A3:A17 then the formulas in those cells are very much applicable to the question. As it stands, there is nothing to relate H21:H35 to the contents of A3:A17 so rearranging the data will not be possible.

 

[gtd526]

If you want to re-order A3:A17 then the formulas in those cells are very much applicable to the question. As it stands, there is nothing to relate H21:H35 to the contents of A3:A17 so rearranging the data will not be possible.

A3:A17 are just text not formulas. I want to know if cell 'contains' the value from H21 or H22 or H23 etc. All A3:A17 does 'contain' values in H21:H35.
looking for vba code for:
How to select a cell in a range if it 'contains' a value from another cell.

 

[jasonb75]

That information would have been good earlier (in post 1, not post 5)! The contents of those cells looked like broken formulas and there was nothing in your posts to indicate otherwise. I'll have a look when I get chance, however now that the scope of the question has changed significantly when compared to what it first appeared to be due to the lack of clarity provided, it may be a few days before that happens.

 

[gtd526]

That information would have been good earlier (in post 1, not post 5)! The contents of those cells looked like broken formulas and there was nothing in your posts to indicate otherwise. I'll have a look when I get chance, however now that the scope of the question has changed significantly when compared to what it first appeared to be due to the lack of clarity provided, it may be a few days before that happens.

Thats fine. sorry for the confusion.

 

[jasonb75]

What purpose do the 'broken' formulas in columns A (and B if applicable) serve and what is will the next step be? i.e. what will you do with them after they are sorted?

I note that in your earlier post you said that they are copied to another worksheet, if the purpose of ordering them is so that they can be copied in the correct order then it may well be a simpler task to skip out the current step and move straight to the formula destination.

Looking at it a bit closer, it appears that A3:A17 are in fact multiple parts of a single formula. If you do sort them, then attempt to piece the formula back together it is extremely unlikely to work correctly anyway. As with many questions, it appears that you are not asking for a way to get the final result but how to get to that result by using an impossible method that you think will work.

 

[gtd526]

What purpose do the 'broken' formulas in columns A (and B if applicable) serve and what is will the next step be? i.e. what will you do with them after they are sorted?

I note that in your earlier post you said that they are copied to another worksheet, if the purpose of ordering them is so that they can be copied in the correct order then it may well be a simpler task to skip out the current step and move straight to the formula destination.

Looking at it a bit closer, it appears that A3:A17 are in fact multiple parts of a single formula. If you do sort them, then attempt to piece the formula back together it is extremely unlikely to work correctly anyway. As with many questions, it appears that you are not asking for a way to get the final result but how to get to that result by using an impossible method that you think will work.

I concatenate A3:A17 into 1 big formula. I want the most successful on top, the least successful on bottom. I was hoping to sort using "if contains" to rearrange the rows in the same order of H21:H35. I just want to re-order the rows. The rest is good.

 

[jasonb75]

I want the most successful on top, the least successful on bottom.

If you need to re-order the formula then your logic is wrong. I haven't looked at it in detail but a formula like that should return the same results regardless of order if it has been written correctly.

I just want to re-order the rows. The rest is good.

Please refer to the final sentence of my previous reply.

it appears that you are not asking for a way to get the final result but how to get to that result by using an impossible method that you think will work.

I have no problem with helping you to come up with a proper solution but I'm not going to waste time on something that will never work.