Excel 2007 formula required

goyalsr

New Member
Joined
Dec 11, 2017
Messages
4
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Ken Cowen

Board Regular
Joined
Jan 18, 2015
Messages
213
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
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
 
Last edited:

goyalsr

New Member
Joined
Dec 11, 2017
Messages
4
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

New Member
Joined
Dec 11, 2017
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
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

New Member
Joined
Dec 11, 2017
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top