Finding first occurence of a String for each row

[sqlindia]

Hi experts,

I have a scenario like find first occurences of "PAID" word in the given rows.

Here the explanation!

excel workbook has two sheets.

In Calculator Sheet I have around 184 columns with some date as a value.

in this sheet I will add "PAID" in any of the month for each amount
.
I need a formula to find out first occurrences of "PAID" word column number
from the given rows

count should start from column C

for example in attached second sheet:

for Amount 1 first occurence of "PAID" found at Column C So the number =1
for Amount 2 first occurence of "PAID" found at Column E So the number =3
for Amount 5 first occurence of "PAID" found at Column H So the number =6
like wise ..
Amount 2 at 3
Amount 3 at 2
Amount 4 at 4
Amount 5 at 6
Amount 6 at 1
Amount 7 at 14


The final count for each corrsponding Amount should store in Summary sheet.

Pease find the attachemnt. help mefirst piad colum number.xlsx

Thanks!

[Sindhus]

This formula may help you. =MATCH("Paid",INDIRECT("Calculator!" & MATCH(E14,Calculator!A:A,0) &":" &MATCH(E14,Calculator!A:A,0)),0)-2

Regards,
Sindhu

[sqlindia]

Hi Sindhus ,
I have used your formula, but it is not working as expected
Could you please give instructiona on how to use formula given by you.

It seems formula using MATCH function on E14 value (Amount 1 ...). sheet 1 E14 values may not match with sheet2 A5.
Can you please give a formula which will not use Amount 1 ,2 column.

[Sindhus]

In your Calculator sheet, there is a mis match between Amount 1 and Amount 11. I thought it was entered as Amount 11 by mistake.

If you do not match them, how do you identify in which row the amount1, amount 2, etc are there in calculator sheet. I can modify the formula, if you tell me how to identify the rows.

[sqlindia]

Please consider only one row(row 5) from calculator sheet and find the first occurence of PAID from the cells start from C5 to GD5 .
here we can fing PAID at C5 so the result is 1. this 1 number should be displayed in Summary page of F14.

Here is the another example:
at row 13 in calculator sheet and find the first occurence of PAID from the cells start from C5 to GD5 .
here we can fing PAID at H13 so the result is 6 , this 6 number should be displayed in Summary page of F18.

if you provide me the formula then I will apply the same other rows as well

Thanks for your help!

[Sindhus]

If you want to hard code the row numbers, you can try this way =match("Paid",calculator!5:5,0)-2. But you can't copy it to other cells. You have to replace "Calculator!5:5" with relevant row numbers.

[sqlindia]

Thanks a lot Sindhu! this is what I am exactly looking for.
Sure I have to replace the relevent row numbers .

[sqlindia]

Sindhu , I need one small enhancement to the formula =match("Paid",calculator!5:5,0)-2

Could you please give me the formula which is take care of UPPER and TRIM functions.

[Sindhus]

Match is not case sensitive. So, i think upper case is already taken care. If you want to search " Paid " (entered with spaces before & after), you can use match("*Paid*",calculator!5:5,0)

[sqlindia]

Thanks a lot Sindhu .