Excel 2007 formula required

[goyalsr]

i have a table like this
BANKNIFTY
BANKNIFTY
BANKNIFTY
BANKNIFTY
BANKNIFTY
BANKNIFTY
NIFTY
NIFTY
NIFTY
i want to find address of the cell on which a text occurs first time and last time in a column.eg in above address of first row and sixth row in case of banknifty, and 7th row and 9th row in case of Nifty. i tried using match function , but it always gives last row number irrespective of my search.please note that data is very big ,above is just a part of that please guide.
I require this for calculation of option pain in a stock.

 

[Ken Cowen]

Assume data in column g rows 1 through 7

=MATCH(G1,$G$1:$G$7,FALSE) should give you the first one

=MATCH(G1,$G$1:$G$7) should give you the last one

I hope this helps.

Ken

 

[Trebor76]

Hi goyalsr,

Welcome to MrExcel!!

Assuming the data starts at A1 and goes down column A (change to suit), put the following formula in Row 1 of any unused column...

=IF(ROW()=1,ADDRESS(ROW(),1),IF(OR(INDIRECT("A"&ROW())<>INDIRECT("A"&ROW()-1),INDIRECT("A"&ROW())<>INDIRECT("A"&ROW()+1)),ADDRESS(ROW(),1),""))

...and copy it down to the last Row used in column A.

HTH

Robert

 

[goyalsr]

Assume data in column g rows 1 through 7

=MATCH(G1,$G$1:$G$7,FALSE) should give you the first one

=MATCH(G1,$G$1:$G$7) should give you the last one

I hope this helps.

Ken

Thanks sir for reply.As i my data is large so i treid
=MATCH(G1,G:G,FALSE)
=MATCH(G1,G:G)

however result is not matching, i think its matching only few alphabets.Please guide sir

 

[goyalsr]

Hi goyalsr,

Welcome to MrExcel!!

Assuming the data starts at A1 and goes down column A (change to suit), put the following formula in Row 1 of any unused column...

=IF(ROW()=1,ADDRESS(ROW(),1),IF(OR(INDIRECT("A"&ROW())<>INDIRECT("A"&ROW()-1),INDIRECT("A"&ROW())<>INDIRECT("A"&ROW()+1)),ADDRESS(ROW(),1),""))

...and copy it down to the last Row used in column A.

HTH

Robert

Thanks sir for your kind response.Result is perfect sir, however i want result in two columns against each row, the first colum gives address of first occurrence and another
colum gives address of last occurrence.
Can you please further help.
Thanks

 

[Trebor76]

Formula for first change:

=IF(ROW()=1,ADDRESS(ROW(),1),IF(INDIRECT("A"&ROW())<>INDIRECT("A"&ROW()-1),ADDRESS(ROW(),1),""))

Formula for last change:

=IF(ROW()=1,"",IF(INDIRECT("A"&ROW())<>INDIRECT("A"&ROW()+1),ADDRESS(ROW(),1),""))

 

[goyalsr]

Formula for first change:

=IF(ROW()=1,ADDRESS(ROW(),1),IF(INDIRECT("A"&ROW())<>INDIRECT("A"&ROW()-1),ADDRESS(ROW(),1),""))

Formula for last change:

=IF(ROW()=1,"",IF(INDIRECT("A"&ROW())<>INDIRECT("A"&ROW()+1),ADDRESS(ROW(),1),""))

Thanks Sir for your kind cooperation and Support