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.

 

[sksanjeev786]

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?

Hi Joe,

E12 and E18 are getting different data compared to our data..in column H

Below are the MAcro logic return for Column E data. But I need data via Formula..

this is a task given to me to get the output via Formula not with MAcro.....
so I have tried my best to get the same score as per column E to H column

Actual data was run via Macro

iRank = 1
For lRow = 2 To sRnge.Rows.Count
  lTemp = False
  If lRow = 2 Then
   sRnge.Cells(lRow - 1, 5) = iRank
   sTemp = sRnge.Cells(lRow - 1, 3)
  End If
  sStatLet = sRnge.Cells(lRow, 1)
  For lCount = 1 To Len(sTemp)
   If Mid(sTemp, lCount, 1) = sStatLet Then
    lTemp = True
    Exit For
   End If
  Next lCount
  If lTemp = False Then
   sRnge.Cells(lRow, 5) = iRank
  Else
   iRank = Application.WorksheetFunction.CountA(sRnge.Range(Cells(1, 5), Cells(lRow - 1, 5))) + 1
   sRnge.Cells(lRow, 5) = iRank
   sTemp = sRnge.Cells(lRow, 3)
  End If
  sStatLet = ""
Next lRow

 

[Joe4]

E12 and E18 are getting different data compared to our data..in column H

That does not answer the question I asked. In your last example what SHOULD be appearing in cells E12 and E18?

I am also a bit confused. I am not sure if you are switching gears on me. Your original question was about a formula. Are you now asking about how to change code?
The formula I gave you returns the results you want, does it not?

 

[sksanjeev786]

That does not answer the question I asked. In your last example what SHOULD be appearing in cells E12 and E18?

I am also a bit confused. I am not sure if you are switching gears on me. Your original question was about a formula. Are you now asking about how to change code?
The formula I gave you returns the results you want, does it not?

Hi Joe,

In Cell E12 (4) and E18( 4) data is different compare to hour data in H12 (1)and H18 (1)
so I need same data in H12 and H18 like how we have in Cell E12 and E8.

The code I have sent you for reference purposes can we make some tweak in Formula like so that I can get same result as per E12 and E18

Conditions are already written in Colunm G so basically it is looking at letters from columns A and C and Macro so written with the same logic but with formula, I am getting bit different data mentioned in above E12 and E18 as other data are looks correct if we compare with formula.

 

[Joe4]

I am sorry, but I am very confused by your explanation.
Is this a totally different question than the original one you asked?

Let's take a totally different approach here, and remove all the extra "noise" you have going on here.
I want you to show much the following three things and ONLY these following things:
1. Show the data results that you need "fixed". Tell me which column it is, and tell me what you are currently getting there now, and how those values are currently being calculated/populated.
2. Show the data results you "wish" to get in this column.
3. Explain the logic of exactly how the results you wish to get are to be calculated (and show the data in any columns used in this calculation).

I do not want to see anything else. Do not show any other columns not used in this determination.

 

[sksanjeev786]

I am sorry, but I am very confused by your explanation.
Is this a totally different question than the original one you asked?

Let's take a totally different approach here, and remove all the extra "noise" you have going on here.
I want you to show much the following three things and ONLY these following things:
1. Show the data results that you need "fixed". Tell me which column it is, and tell me what you are currently getting there now, and how those values are currently being calculated/populated.
2. Show the data results you "wish" to get in this column.
3. Explain the logic of exactly how the results you wish to get are to be calculated (and show the data in any columns used in this calculation).

I do not want to see anything else. Do not show any other columns not used in this determination.

Hi Joe,

Sure! Thank you so much for your time on this let's start fresh..

1. I need to check Cell A3 in Cell C2, A4 to C3, A5 to C4 , A6 to C5 and same condition

So my row data is from cell A1 to D14 and I need ranking if the Column A letter is appearing on column C

Example 1: Cell A3 (B letter) letter is not coming in Cell C2 ('52 ADF so no B letter) then the ranking will be the same as my output is in G3
and until we get a letter.

Example 2: Cell A6 (A letter) is coming to cell C5 ('42 AD) then the ranking will change to 4 since above are same ranking but the position is 4 and next number will be 5 and it will be same since no letter is coming

2. I have kept the result in column G (output)

3. I need to check if Cell A3 (B letter) is available in Cell C2 ('52 ADF) , A4 to C3, A5 to C4 , A6 to C5 and same condition ( can you please see the example in above points )

Below is the data for ref. with output

book1
	A	B	C	D	E	F	G
1							Output
2	C	Capital One	52 ADF	52			1
3	B	Bank of America	48 AD	48			1
4	E	Chase	48 AD	48			1
5	F	Wells Fargo	42 AD	42			4
6	A	TD Bank	32	32			5
7	D	Citibank	31	31			5
8
9	B	Bank of America	60 ACDE	60			1
10	F	Wells Fargo	53 ACD	53			1
11	E	Chase	45 ACD	45			3
12	D	Citibank	23	23			4
13	C	Capital One	22	22			4
14	A	TD Bank	20	20			4
Sheet1

Let me know if you need another information

Thanks in advance

Regards,
Sanjeev

 

[Joe4]

Your example does not look right to me, based on your explanation.
If the ranking only increases for a row when the letter found in column A is found in column C of the row above (like you seem to be describing), then I would expect the results to look like this:

According to your explanation, this is what I understand should be happening:
G5 would stay at "1", since when you look at row 5, the value in cell A5 ("F") does not appear in cell C4 ("48 AD").
G6 would jump up to "5", since when you look at row 6, the value in cell A6 ("A") does appear in cell C5 ("42 AD").
G7 would remain at "5", since when you look at row 7, the value in cell A7 ("D") does appear in cell C6 ("32").

But that is not what you are showing in your example...

 

[sksanjeev786]

Your example does not look right to me, based on your explanation.
If the ranking only increases for a row when the letter found in column A is found in column C of the row above (like you seem to be describing), then I would expect the results to look like this:
View attachment 117688

According to your explanation, this is what I understand should be happening:
G5 would stay at "1", since when you look at row 5, the value in cell A5 ("F") does not appear in cell C4 ("48 AD").
G6 would jump up to "5", since when you look at row 6, the value in cell A6 ("A") does appear in cell C5 ("42 AD").
G7 would remain at "5", since when you look at row 7, the value in cell A7 ("D") does appear in cell C6 ("32").

But that is not what you are showing in your example...

Hi Joe,

Thanks for checking on this,

I Agree with your point but since the letter 42 AD (Cell C5) I I need raking based on that so that G5 Ranking I need as 4 and rest will 5
and same scenario for 45 ACD (Cell C11) so the ranking will be 3 since 45 ACD is in 3 position...

So I need same output I have shared in my data file...

I hope you understand our scenario for this

Regards
Sanjeev

 

[Joe4]

Then I am sorry, I do not understand the logic at all. Your data example and description do not seem to be consistent.
I think I am going to have to bow out here.

Since this question/scenario is actually different than the original question you asked in this thread (which was already answered and solution accepted), we will allow you to post this new question to a new thread of its own. Then it will show up as a new unanswered question, and others will see it and may answer it.

 

[sksanjeev786]

Then I am sorry, I do not understand the logic at all. Your data example and description do not seem to be consistent.
I think I am going to have to bow out here.

Since this question/scenario is actually different than the original question you asked in this thread (which was already answered and solution accepted), we will allow you to post this new question to a new thread of its own. Then it will show up as a new unanswered question, and others will see it and may answer it.

Hi Joe,

Sure! np

I know it is a bit tricky for us. But you know my Client needs it in that format which I have shared with you..

but thank you so much for your valuable time I will go ahead and fix it manually for all the data I have

Regards
Sanjeev

 

[sksanjeev786]

Hi Joe,

Sure! np

I know it is a bit tricky for us. But you know my Client needs it in that format which I have shared with you..

but thank you so much for your valuable time I will go ahead and fix it manually for all the data I have

Regards
Sanjeev

Hi Joe,

I just tried something wiht data
Can we do something in column J so were ever I gettign false i can get same number as per column G
my conditions is on column I

Book1
	A	B	C	D	E	F	G	H	I	J
1							Output
2	C	Capital One	52 ADF	52			1		FALSE	1
3	B	Bank of America	48 AD	48			1		FALSE	2
4	E	Chase	48 AD	48			1		FALSE	3
5	F	Wells Fargo	42 AD	42			4		TRUE	FALSE
6	A	TD Bank	32	32			5		FALSE	5
7	D	Citibank	31	31			5		TRUE	FALSE
8
9	B	Bank of America	60 ACDE	60			1		FALSE	1
10	F	Wells Fargo	53 ACD	53			1		FALSE	2
11	E	Chase	45 ACD	45			3		TRUE	FALSE
12	D	Citibank	23	23			4		FALSE	4
13	C	Capital One	22	22			4		FALSE	5
14	A	TD Bank	20	20			4		TRUE	FALSE
Sheet1

Cell Formulas
Range	Formula
I2:I7,I9:I14	I2	=ISNUMBER(SEARCH(A3,C2))
J2:J7	J2	=IF(I2=FALSE,ROW()-1)
J9:J14	J9	=IF(I9=FALSE,ROW()-8)