Row number of cell containing specific text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

exocet.am39@gmail.com - 25 Jan 2008 17:42 GMT

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

Reply to this Message

Bernie Deitrick - 25 Jan 2008 17:59 GMT

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

> hi,
>
[quoted text clipped - 9 lines]
>
> d b

Reply to this Message

exocet.am39@gmail.com - 25 Jan 2008 19:55 GMT

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

Reply to this Message

Bernie Deitrick - 26 Jan 2008 23:44 GMT

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

>i just want to see the row numbers. i will not be doing any operations
> with them.
[quoted text clipped - 8 lines]
>
> db

Reply to this Message

Gary''s Student - 25 Jan 2008 18:06 GMT

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

Signature

Gary''s Student - gsnu2007c

> hi,
>
[quoted text clipped - 9 lines]
>
> d b

Reply to this Message