macro to add correct number of ")" at end of nested IF statement

gtd526 · Mar 12, 2024

Hello,
Is there a way to modify the text in Columns A & B, in the same Row as "????" via (D:D) to the correct number of ")" at the end of the text using a macro.
This will allow my macro for cell value to formula conversion to work correctly.
"????" is the cut-off value to end the concatenate formula, the Row for "????" (D:D), could change daily.
I've giving you the spreadsheet and the concatenate formulas, I'm not sure which will be easier to code.
Here is the spreadsheet (part of).

NBA.xlsm

A

B

C

D

E

F

1

Row 4

Row 5

based on %

Dupes

0.7

10

2

As FAV

Rank

Criteria2

Criteria2 Percentage

Count

3

IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$W4>=$W5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"W, AL:AM>=60%"

IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$W4<=$W5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"W, AL:AM>=60%"

4

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Rank2, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Rank2, AL:AM>=60%"

5

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>0),$Q4>$Q5,ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>0),$Q4<$Q5,ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, AL:AM>=60%"

6

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, S, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, S, AL:AM>=60%"

7

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4>=$R5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"R, S, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4<=$R5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, S, AL:AM>=60%"

8

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>$Q5,$R4>0,$R4>$R5,$S4>0,$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, S, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<$Q5,$R5>0,$R4<$R5,$S5>0,$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, S, AL:AM>=60%"

9

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4>=$Z5,$AA4>=$AA5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Z, AA, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4<=$Z5,$AA4<=$AA5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Z, AA, AL:AM>=60%"

10

,IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))

#VALUE!

????

11

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$O4>=$O5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"MOV, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$O4<=$O5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"MOV, AL:AM>=60%"

12

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"MOV2, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"MOV2, AL:AM>=60%"

13

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4>=$R5,ISNUMBER($T4),($T4>0),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"R, T>0, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$R4<=$R5,ISNUMBER($T5),($T5>0),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, T>0, AL:AM>=60%"

14

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$S4>=$S5,ISNUMBER($T4),($T4>0),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"S, T>0, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$S4<=$S5,ISNUMBER($T5),($T5>0),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, T>0, AL:AM>=60%"

15

,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,ISNUMBER($T4),($T4>0),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, S, T>0, AL:AM>=60%"

,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,ISNUMBER($T5),($T5>0),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, S, T>0, AL:AM>=60%"

10

Q, S, T>0, AL:AM>=60%

66.7%

6

Indicators

Cell Formulas
Range	Formula
C3:C15	C3	=RANK(E3,$E$3:$E$32)
D3:D15	D3	=MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
E3:E15	E3	=IF($D3="????","",IF($D3="Rank2, AL:AM>=60%",Favs!$AH$55,IF($D3="MOV, AL:AM>=60%",Favs!$AB$67,IF($D3="MOV2, AL:AM>=60%",Favs!$AE$67,IF($D3="MOV,MOV2, AL:AM>=60%",Favs!$AH$67,IF($D3="M,N>0,Q,R>0,AL:AM>=60%",Favs!$Y$79,IF($D3="MOV2 > ATS",Favs!$V$79,IF($D3="M,N>0,Q,R>0,AL:AM>=60%",Favs!$Y$79,IF($D3="Q, R, S, AL:AM>=60%",Favs!$P$43,IF($D3="Q, R, S, T>0, AL:AM>=60%",Favs!$M$43,IF($D3="Q, R, T>0, AL:AM>=60%",Favs!$S$43,IF($D3="Q, S, T>0, AL:AM>=60%",Favs!$V$43,IF($D3="R, S, T>0, AL:AM>=60%",Favs!$Y$43,IF($D3="Q, R, AL:AM>=60%",Favs!$P$55,IF($D3="Q, S, AL:AM>=60%",Favs!$S$55,IF($D3="Q, T>0, AL:AM>=60%",Favs!$M$67,IF($D3="R, T>0, AL:AM>=60%",Favs!$P$67,IF($D3="S, T>0, AL:AM>=60%",Favs!$S$67,IF($D3="AB, AL:AM>=60%",Favs!$V$67,IF($D3="W, AL:AM>=60%",Favs!$Y$55,IF($D3="U, V, AL:AM>=60%",Favs!$AB$55,IF($D3="Z, AA, AL:AM>=60%",Favs!$AE$55,IF($D3="R, S, AL:AM>=60%",Favs!$V$55,"")))))))))))))))))))))))
F3	F3	=Favs!$Y$60
F4	F4	=Favs!$AH$60
F5	F5	=Favs!$P$60
F6	F6	=Favs!$S$60
F7	F7	=Favs!$V$60
F8	F8	=Favs!$P$48
F9	F9	=Favs!$AE$60
F11	F11	=Favs!$AB$72
F12	F12	=Favs!$AE$72
F13	F13	=Favs!$P$72
F14	F14	=Favs!$S$72
F15	F15	=Favs!$V$48

Below are the Concatenate formulas:

NBA.xlsm

A

37

Row 4

38

IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$W4>=$W5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"W, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Rank2, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>0),$Q4>$Q5,ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4>=$R5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"R, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>$Q5,$R4>0,$R4>$R5,$S4>0,$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4>=$Z5,$AA4>=$AA5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Z, AA, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))

39

Row 5

40

IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$W4<=$W5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"W, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Rank2, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>0),$Q4<$Q5,ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4<=$R5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<$Q5,$R5>0,$R4<$R5,$S5>0,$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4<=$Z5,$AA4<=$AA5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Z, AA, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))

41

Paste SV

42

Row 4

43

IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$W4>=$W5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"W, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Rank2, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>0),$Q4>$Q5,ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4>=$R5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"R, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>$Q5,$R4>0,$R4>$R5,$S4>0,$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4>=$Z5,$AA4>=$AA5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Z, AA, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))

44

Row 5

45

IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$W4<=$W5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"W, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Rank2, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>0),$Q4<$Q5,ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4<=$R5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<$Q5,$R5>0,$R4<$R5,$S5>0,$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4<=$Z5,$AA4<=$AA5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Z, AA, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))

Indicators

Cell Formulas
Range	Formula
A38	A38	=CONCATENATE(TEXTJOIN(,,INDIRECT("A3:A"&MATCH("????",$D$3:$D$32,0)+2)))
A40	A40	=CONCATENATE(TEXTJOIN(,,INDIRECT("B3:B"&MATCH("????",$D$3:$D$32,0)+2)))

gtd526 · Mar 14, 2024

gtd526 said:
Hello,
Is there a way to modify the text in Columns A & B, in the same Row as "????" via (D:D) to the correct number of ")" at the end of the text using a macro.
This will allow my macro for cell value to formula conversion to work correctly.
"????" is the cut-off value to end the concatenate formula, the Row for "????" (D:D), could change daily.
I've giving you the spreadsheet and the concatenate formulas, I'm not sure which will be easier to code.
Here is the spreadsheet (part of).

NBA.xlsm
A B C D E F
1 Row 4 Row 5 based on % Dupes 0.7 10
2 As FAV As FAV Rank Criteria2 Criteria2 Percentage Count
3 IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$W4>=$W5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"W, AL:AM>=60%" IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$W4<=$W5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"W, AL:AM>=60%" 1 W, AL:AM>=60% 90.0% 10
4 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Rank2, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Rank2, AL:AM>=60%" 3 Rank2, AL:AM>=60% 74.5% 55
5 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>0),$Q4>$Q5,ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>0),$Q4<$Q5,ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, AL:AM>=60%" 4 Q, R, AL:AM>=60% 73.1% 26
6 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, S, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, S, AL:AM>=60%" 4 Q, S, AL:AM>=60% 73.1% 26
7 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4>=$R5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"R, S, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4<=$R5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, S, AL:AM>=60%" 4 R, S, AL:AM>=60% 73.1% 26
8 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>$Q5,$R4>0,$R4>$R5,$S4>0,$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, S, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<$Q5,$R5>0,$R4<$R5,$S5>0,$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, S, AL:AM>=60%" 7 Q, R, S, AL:AM>=60% 72.4% 29
9 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4>=$Z5,$AA4>=$AA5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Z, AA, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4<=$Z5,$AA4<=$AA5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Z, AA, AL:AM>=60%" 2 Z, AA, AL:AM>=60% 77.8% 9
10 ,IF(AND($A4=$AQ4,$B4<0,$B4=100),"????","")))))))) ,IF(AND($A4=$AQ4,$B4<0,$B4=100),"????","")))))))) #VALUE! ????
11 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$O4>=$O5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"MOV, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$O4<=$O5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"MOV, AL:AM>=60%" 8 MOV, AL:AM>=60% 69.6% 56
12 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"MOV2, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"MOV2, AL:AM>=60%" 9 MOV2, AL:AM>=60% 67.2% 58
13 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4>=$R5,ISNUMBER($T4),($T4>0),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"R, T>0, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$R4<=$R5,ISNUMBER($T5),($T5>0),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, T>0, AL:AM>=60%" 10 R, T>0, AL:AM>=60% 66.7% 6
14 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$S4>=$S5,ISNUMBER($T4),($T4>0),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"S, T>0, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$S4<=$S5,ISNUMBER($T5),($T5>0),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, T>0, AL:AM>=60%" 10 S, T>0, AL:AM>=60% 66.7% 6
15 ,IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,ISNUMBER($T4),($T4>0),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, S, T>0, AL:AM>=60%" ,IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,ISNUMBER($T5),($T5>0),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, S, T>0, AL:AM>=60%" 10 Q, S, T>0, AL:AM>=60% 66.7% 6
Indicators
Cell Formulas
Range Formula
C3:C15 C3 =RANK(E3,$E$3:$E$32)
D3:D15 D3 =MID($A3,FIND("""",$A3)+1,FIND("""",$A3,FIND("""",$A3)+1)-FIND("""",$A3)-1)
E3:E15 E3 =IF($D3="????","",IF($D3="Rank2, AL:AM>=60%",Favs!$AH$55,IF($D3="MOV, AL:AM>=60%",Favs!$AB$67,IF($D3="MOV2, AL:AM>=60%",Favs!$AE$67,IF($D3="MOV,MOV2, AL:AM>=60%",Favs!$AH$67,IF($D3="M,N>0,Q,R>0,AL:AM>=60%",Favs!$Y$79,IF($D3="MOV2 > ATS",Favs!$V$79,IF($D3="M,N>0,Q,R>0,AL:AM>=60%",Favs!$Y$79,IF($D3="Q, R, S, AL:AM>=60%",Favs!$P$43,IF($D3="Q, R, S, T>0, AL:AM>=60%",Favs!$M$43,IF($D3="Q, R, T>0, AL:AM>=60%",Favs!$S$43,IF($D3="Q, S, T>0, AL:AM>=60%",Favs!$V$43,IF($D3="R, S, T>0, AL:AM>=60%",Favs!$Y$43,IF($D3="Q, R, AL:AM>=60%",Favs!$P$55,IF($D3="Q, S, AL:AM>=60%",Favs!$S$55,IF($D3="Q, T>0, AL:AM>=60%",Favs!$M$67,IF($D3="R, T>0, AL:AM>=60%",Favs!$P$67,IF($D3="S, T>0, AL:AM>=60%",Favs!$S$67,IF($D3="AB, AL:AM>=60%",Favs!$V$67,IF($D3="W, AL:AM>=60%",Favs!$Y$55,IF($D3="U, V, AL:AM>=60%",Favs!$AB$55,IF($D3="Z, AA, AL:AM>=60%",Favs!$AE$55,IF($D3="R, S, AL:AM>=60%",Favs!$V$55,"")))))))))))))))))))))))
F3 F3 =Favs!$Y$60
F4 F4 =Favs!$AH$60
F5 F5 =Favs!$P$60
F6 F6 =Favs!$S$60
F7 F7 =Favs!$V$60
F8 F8 =Favs!$P$48
F9 F9 =Favs!$AE$60
F11 F11 =Favs!$AB$72
F12 F12 =Favs!$AE$72
F13 F13 =Favs!$P$72
F14 F14 =Favs!$S$72
F15 F15 =Favs!$V$48

Below are the Concatenate formulas:

NBA.xlsm
A
37 Row 4
38 IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$W4>=$W5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"W, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Rank2, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>0),$Q4>$Q5,ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4>=$R5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"R, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>$Q5,$R4>0,$R4>$R5,$S4>0,$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4>=$Z5,$AA4>=$AA5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Z, AA, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))
39 Row 5
40 IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$W4<=$W5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"W, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Rank2, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>0),$Q4<$Q5,ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4<=$R5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<$Q5,$R5>0,$R4<$R5,$S5>0,$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4<=$Z5,$AA4<=$AA5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Z, AA, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))
41 Paste SV
42 Row 4
43 IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$W4>=$W5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"W, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Rank2, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),ISNUMBER($Q4),($Q4>0),$Q4>$Q5,ISNUMBER($R4),($R4>0),$R4>$R5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>=$Q5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4>=$R5,$S4>=$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"R, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Q4>$Q5,$R4>0,$R4>$R5,$S4>0,$S4>$S5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$K4<=$K5,ISNUMBER($Q4),($Q4>=0.55),ISNUMBER($R4),($R4>0),ISNUMBER($S4),($S4>0),$AR4=TRUE,$AW4<=$AW$1,$Q4>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4>=$Z5,$AA4>=$AA5,ISNUMBER($AL4),($AL4>=.6),ISNUMBER($AM4),($AM4>=.6)),"Z, AA, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))
44 Row 5
45 IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$W4<=$W5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"W, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Rank2, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),ISNUMBER($Q5),($Q5>0),$Q4<$Q5,ISNUMBER($R5),($R5>0),$R4<$R5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<=$Q5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$R4<=$R5,$S4<=$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A5,$H$52:$H$66,0)),$Q4<$Q5,$R5>0,$R4<$R5,$S5>0,$S4<$S5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Q, R, S, AL:AM>=60%",IF(AND($A5=$AQ5,$B5<0,$K4>=$K5,ISNUMBER($Q5),($Q5>=0.55),ISNUMBER($R5),($R5>0),ISNUMBER($S5),($S5>0),$AR5=TRUE,$AW5<=$AW$1,$Q5>=.55,ISNA(MATCH($A4,$H$52:$H$66,0)),$Z4<=$Z5,$AA4<=$AA5,ISNUMBER($AL5),($AL5>=.6),ISNUMBER($AM5),($AM5>=.6)),"Z, AA, AL:AM>=60%",IF(AND($A4=$AQ4,$B4<0,$B4=100),"????",""))))))))
Indicators
Cell Formulas
Range Formula
A38 A38 =CONCATENATE(TEXTJOIN(,,INDIRECT("A3:A"&MATCH("????",$D$3:$D$32,0)+2)))
A40 A40 =CONCATENATE(TEXTJOIN(,,INDIRECT("B3:B"&MATCH("????",$D$3:$D$32,0)+2)))

I've created a solution to modify the text in Columns A & B, in the same Row as "????" via (D:D), to the correct number of ")" at the end of the text using a macro.

VBA Code:

'find the ROW containing ????
    For i = 1 To 32
        If InStr(1, LCase(Range("D" & i)), "?") <> 0 Then
            Range("A" & i).Select   'selects A(x), row for "????"
            myCell = ActiveCell.Address 'myCell = A10

'obtain the ROW count -2 for number of ")" to add at end of cell value = myRange
            Range(Selection, Selection.End(xlUp)).Select
            rowCount = Selection.Rows.Count
            myRange = rowCount - 2  'myRange = 8, use for number of ")" at the end
'removes the ending )))
            str1 = Range("A" & i).Value
            str1 = Left(str1, InStrRev(str1, """"))
            Range("A" & i).Value = str1
            Range("A" & i).Select
'adds the ending )))
            str = String(myRange, ")")  'str is correct number of ")" to add at end of A&B text
            Range("A" & i).Select
            ActiveCell.Value = CStr(ActiveCell.Value) & str
            Range("A" & i).Select
            
            str1 = Range("B" & i).Value
            str1 = Left(str1, InStrRev(str1, """"))
            Range("B" & i).Value = str1
'            Range("B" & i).Select
            str = String(myRange, ")")  'str is correct number of ")" to add at end of A&B text
            Range("B" & i).Select
            ActiveCell.Value = CStr(ActiveCell.Value) & str
'            Range("B" & i).Select

        
        End If
    Next i

macro to add correct number of ")" at end of nested IF statement

gtd526

Well-known Member

Excel Facts

gtd526

Well-known Member

Similar threads

Forum statistics

Share this page

macro to add correct number of ")" at end of nested IF statement

gtd526

Well-known Member

Excel Facts

gtd526

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock