Add the number to True

[sksanjeev786]

Hi Team

I need a number ranking on column N4 to N9 so where ever i have True I need data num 2 (N4+1) and false will have same number and next true it will 4 since 2 number came twice see the output in column O

TD Macro.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3															Output
4	A	TD Bank	17				17	B	Bank of America	55 ACDEF	55		1	1	1
5	B	Bank of America	55 ACDEF				55	E	Chase	38 ACD	38		7	TRUE	2
6	C	Capital One	23				23	F	Wells Fargo	33 ACD	33		#VALUE!	FALSE	2	Same Number
7	D	Citibank	20				20	C	Capital One	23	23		5	TRUE	4
8	E	Chase	38 ACD				38	D	Citibank	20	20		#VALUE!	FALSE	4	Same Number
9	F	Wells Fargo	33 ACD				33	A	TD Bank	17	17		#VALUE!	FALSE	4	Same Number
Sheet2 (3)

Cell Formulas
Range	Formula
H4:K9	H4	=CHOOSECOLS(SORTBY(A4:G9,G4:G9,-1),1,2,3,7)
M5:M9	M5	=SEARCH(H5,J4)
N5:N9	N5	=ISNUMBER(SEARCH(H5,J4))
G4:G9	G4	=VALUE(IFERROR(LEFT(C4,2),LEFT(C4,1)))
Dynamic array formulas.

 

[Joe4]

Why is O7 4 instead of 3?
Wouldn't 3 be the next number after 2?

 

[sksanjeev786]

Why is O7 4 instead of 3?
Wouldn't 3 be the next number after 2?

Hi Joe,

If it is False then the number will be the same as True with above cell number and next True is coming in row 4 so it will be 4 and False will remain same number

 

[Joe4]

OK, then assuming that you have a "1" in cell O4 to start, put this formula in cell O5 and copy down:

=IF(N5=FALSE,O4,COUNTA(N$4:N5))

 

[sksanjeev786]

OK, then assuming that you have a "1" in cell O4 to start, put this formula in cell O5 and copy down:

=IF(N5=FALSE,O4,COUNTA(N$4:N5))

Thanks you Joe

it is working fine for now but will let you know if I need any further assistance.

Regards
Sanjeev

 

[Joe4]

You are welcome!
Glad I was able to help.

By the way, here is another option that you can put in O4 and copy all the down (so you don't need to put a 1 in the first cell to start).

=IF(N4=FALSE,O3,ROW()-3)

 

[sksanjeev786]

Tha

You are welcome!
Glad I was able to help.

By the way, here is another option that you can put in O4 and copy all the down (so you don't need to put a 1 in the first cell to start).

=IF(N4=FALSE,O3,ROW()-3)

Sure! thanks Joe

 

[sksanjeev786]

Tha

Sure! thanks Joe

Hi Joe,

I have applied your formula but looks like i am getting a slightly different score My data is in column E and Formula data in Column H
but in cell E12 and E18 I am getting ranking different logic are applied in column G

Can we tweak in logic so that I can get same result like Colum E

TD Macro.xlsm
	A	B	C	D	E	F	G	H	I	J
1					Actual output			Formula Output
2	B	Bank of America	55 ACDEF	55	1		1	1		TRUE
3	E	Chase	38 ACD	38	2		TRUE	2		TRUE
4	F	Wells Fargo	33 ACD	33	2		FALSE	2		TRUE
5	C	Capital One	23	23	4		TRUE	4		TRUE
6	D	Citibank	20	20	4		FALSE	4		TRUE
7	A	TD Bank	17	17	4		FALSE	4		TRUE
8
9	C	Capital One	52 ADF	52	1		1	1		TRUE
10	B	Bank of America	48 AD	48	1		FALSE	1		TRUE
11	E	Chase	48 AD	48	1		FALSE	1		TRUE
12	F	Wells Fargo	42 AD	42	4		FALSE	1		FALSE
13	A	TD Bank	32	32	5		TRUE	5		TRUE
14	D	Citibank	31	31	5		FALSE	5		TRUE
15
16	B	Bank of America	60 ACDE	60	1		1	1		TRUE
17	F	Wells Fargo	53 ACD	53	1		FALSE	1		TRUE
18	E	Chase	45 ACD	45	3		FALSE	1		FALSE
19	D	Citibank	23	23	4		TRUE	4		TRUE
20	C	Capital One	22	22	4		FALSE	4		TRUE
21	A	TD Bank	20	20	4		FALSE	4		TRUE
22
23	C	Capital One	57 AD	57	1		1	1		TRUE
24	E	Chase	57 AD	57	1		FALSE	1		TRUE
25	F	Wells Fargo	55 AD	55	1		FALSE	1		TRUE
26	B	Bank of America	54 AD	54	1		FALSE	1		TRUE
27	A	TD Bank	39	39	5		TRUE	5		TRUE
28	D	Citibank	36	36	5		FALSE	5		TRUE
Data

Cell Formulas
Range	Formula
G3:G7,G24:G28,G17:G21,G11:G14	G3	=ISNUMBER(SEARCH(A3,C2))
H3:H7	H3	=IF(G3=FALSE,H2,COUNTA(G$3:G4))
J2:J7,J23:J28,J16:J21,J9:J14	J2	=E2=H2
G10	G10	=ISNUMBER(SEARCH(A9,C9))
H10:H14	H10	=IF(G10=FALSE,H9,COUNTA(G$10:G11))
H17:H21	H17	=IF(G17=FALSE,H16,COUNTA(G$17:G18))
H24:H28	H24	=IF(G24=FALSE,H23,COUNTA(G$24:G25))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J28	Cell Value	contains "f"	text	NO
G23:G28	Cell Value	contains "True"	text	NO
G16:G21	Cell Value	contains "True"	text	NO
G9:G14	Cell Value	contains "True"	text	NO
G2:G7	Cell Value	contains "True"	text	NO

 

[sksanjeev786]

Tha

Sure! thanks Joe

 

[Joe4]

I am confused as to what you are telling me - you do not appear to have any formulas in column E at all. Those appear to be hard-coded, not the results of my formula.
What should the values in cells E12 and E18 be?