How to find the first row not equal to the value in the first row?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

INTP56 - 27 May 2008 23:08 GMT

Excel 2003

I often get data files from instruments where many initial rows all have the
same value, typically zero, but not limited to that. Often these files are
text files with 100+ columns.

For example, I might have a file with 30,000 rows in it, but some columns
don't start having data till row 20,000 or so.

What I would like is a worksheet function where I could give it a range, and
it return the first cell (Index or value) that is not equal to the value in
the first data row.

I'd like to do this without creating extra columns, or having to manually
iterate through thousands of cells for 100+ columns. Functionally, I would
type a formula into a cell above the first column in question, then drag it
across all the other columns so each column would have it's own individual
value for the first different value.

The icing on the cake would be to do the same thing, except this time look
from the bottom up.

Is this possible?

Thanks, Bob

Reply to this Message

Peo Sjoblom - 27 May 2008 23:41 GMT

=INDEX(A2:A30000,MATCH(TRUE,A2:A30000<>A2,0))

entered with ctrl + shift & enter

will return the contents of the first row in A2:A3000 not equal to A2

Signature

Regards,

Peo Sjoblom

> Excel 2003
>
[quoted text clipped - 25 lines]
>
> Thanks, Bob

Reply to this Message

INTP56 - 28 May 2008 00:41 GMT

Thanks Peo, this is exactly the kind of thing I was looking for.

This won't "look up" the array, but just being able to know where I start is
great.

Thanks again,

Bob

> =INDEX(A2:A30000,MATCH(TRUE,A2:A30000<>A2,0))
>
[quoted text clipped - 31 lines]
> >
> > Thanks, Bob

Reply to this Message

RagDyer - 28 May 2008 01:11 GMT

With imported data starting in A3, try this *array* formula in A1 for the
*ROW* number of the start of data:

=MIN(IF(A1:A500<>A1,ROW(1:500)))

and in A2 for the *ROW* number of the end of data:

=MAX(IF(A1:A500<>A500,ROW(1:500)))

Signature

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Thanks Peo, this is exactly the kind of thing I was looking for.
>
[quoted text clipped - 51 lines]
>> >
>> > Thanks, Bob

Reply to this Message

RagDyer - 28 May 2008 01:18 GMT

Sorry ... WRONG formulas!

Use these:

For start in A1:
=MIN(IF(A3:A500<>A3,ROW(3:500)))

For end in A2:
=MAX(IF(A3:A500<>A500,ROW(3:500)))

Still *array* formulas.

Signature

> With imported data starting in A3, try this *array* formula in A1 for the
> *ROW* number of the start of data:
[quoted text clipped - 60 lines]
>>> >
>>> > Thanks, Bob

Reply to this Message

INTP56 - 28 May 2008 13:01 GMT

Rag,

At first, I didn't think this was going to work, but after I paid more
attention I realized this was a very clever way to get what I wanted.

Thanks again, another trick for my toolbag!

Bob

> Sorry ... WRONG formulas!
>
[quoted text clipped - 71 lines]
> >>> >
> >>> > Thanks, Bob

Reply to this Message

RagDyeR - 28 May 2008 16:17 GMT

You're welcome, and thank you for the feed-back.

Signature

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Rag,

At first, I didn't think this was going to work, but after I paid more
attention I realized this was a very clever way to get what I wanted.

Thanks again, another trick for my toolbag!

Bob

"RagDyer" wrote:

> Sorry ... WRONG formulas!
>
[quoted text clipped - 77 lines]
> >>> >
> >>> > Thanks, Bob

Reply to this Message

Jim Cone - 28 May 2008 00:17 GMT

Bob,
A user defined function (UDF) is another way.
Place the code below in a standard module.
Enter "=StartValue()" in a row above your data and fill across.
Code follows...
'--
Function StartValue() As String
On Error GoTo BadStart
Dim s As String
Dim rng As Range
Application.Volatile
Set rng = Application.Caller.Offset(1, 0)
Do
s = rng.Text
Set rng = rng(2, 1)
Loop While s = vbNullString

Do Until rng.Text <> s
Set rng = rng(2, 1)
Loop
s = rng.Text
If s = vbNullString Then s = "blank"
StartValue = s
Set rng = Nothing
Exit Function
BadStart:
StartValue = "Error " & Err.Number
End Function

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"INTP56"
wrote in message
Excel 2003
I often get data files from instruments where many initial rows all have the
same value, typically zero, but not limited to that. Often these files are
text files with 100+ columns.
For example, I might have a file with 30,000 rows in it, but some columns
don't start having data till row 20,000 or so.
What I would like is a worksheet function where I could give it a range, and
it return the first cell (Index or value) that is not equal to the value in
the first data row.
I'd like to do this without creating extra columns, or having to manually
iterate through thousands of cells for 100+ columns. Functionally, I would
type a formula into a cell above the first column in question, then drag it
across all the other columns so each column would have it's own individual
value for the first different value.
The icing on the cake would be to do the same thing, except this time look
from the bottom up.
Is this possible?
Thanks, Bob

Reply to this Message

INTP56 - 28 May 2008 12:52 GMT

Jim,

I will probably need to do something like this to look up the columns.

I am always looking for a way to do things without iterating. (Since I'm
more of a database guy, I am used to using sets and not iterating at all.)
When I can't think of a way to avoid iteration, often I read a range into a
variant and iterate through that. It requires managing where I am in the
array vs where that maps to in the worksheet, but is faster than actually
trying to access Cell.Value for many cells.

Thanks for the UDF idea.

Bob

> Bob,
> A user defined function (UDF) is another way.
[quoted text clipped - 24 lines]
> StartValue = "Error " & Err.Number
> End Function

Reply to this Message