Row number of cell containing specific text

[exocet.am39]

hi,

how can i write a simple formula to find the row numbers for all the
cells in column A that contain the text "YES"?

i've been trying the row, cell, and address functions but haven't
managed to get too far.

any help would be appreciated.

thanks,

d b

 

[Bernie Deitrick]

d b,

What do you want to do with the row numbers?

There are so many options involved here that it helps to know what you actually want, rather than
what you think you need.

HTH,
Bernie
MS Excel MVP

 

[Gary''s Student]

AutoFilter will display the rows, but if you want the list of row numbers,
try this UDF:

Function where_is(s As String) As String
where_is = ""
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
If Cells(i, "A").Value = s Then
where_is = where_is & "," & i
End If
Next
If where_is = "" Then Exit Function
where_is = Right(where_is, Len(where_is) - 1)
End Function


For example, if A1 thru A18 contains:
YES
NO

NO

YES
YES
YES


MAYBE

PERHAPS

DEFINITELY NOT


YES

then =where_is("YES")
will display:
1,6,7,8,18

 

[exocet.am39]

i just want to see the row numbers. i will not be doing any operations
with them.

is there any way that i can find the row numbers (or just the
different cell addresses) using an excel formula? (it's easy to do if
there is only one cell with the text, in which case i can use the
MATCH function. the difficult part is when i have more than one
occurance of the text).

thank you!

db

 

[Bernie Deitrick]

db,

Array enter (enter using Ctrl0Shift-Enter) this formula in a cell in row 2:

=SMALL(IF($A$1:$A$1000="YES",ROW($A$1:$A$1000)),ROW()-ROW($D$1))

Then copy down as far as you need - it will return #NUM! errors when it has
found all the rows with YES

HTH,
Bernie
MS Excel MVP