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

[INTP56]

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

 

[Peo Sjoblom]

=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



--


Regards,


Peo Sjoblom

 

[Jim Cone]

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
--
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

 

[INTP56]

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

 

[RagDyer]

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)))

--
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.

 

[RagDyer]

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.
--
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.

 

[INTP56]

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

 

[INTP56]

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]

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

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