address of first and last number in a row
 

I have various rows with numbers entered randomly with 0's inbetween. The
first row has are represents calender dates. I need to find the date of the
first number entry and the date of the last number entry larger than 0. If
of any help the numbers entered are from 1 to 6. These are randomly entered.

address of first and last number in a row
 

With dates in row 1 an data in row 2...

For the leftmost (first) number 0...

Array entered** :

=INDEX(A1:J1,MATCH(TRUE,A2:J20,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

For the rightmost (last) number 0...

=LOOKUP(1E100,1/A2:J2,A1:J1)

--
Biff
Microsoft Excel MVP


"Kobus" wrote in message
...
I have various rows with numbers entered randomly with 0's inbetween. The
first row has are represents calender dates. I need to find the date of
the
first number entry and the date of the last number entry larger than 0.
If
of any help the numbers entered are from 1 to 6. These are randomly
entered.

address of first and last number in a row
 

Hi Kobus,

Your explanation is rather confusing! You are looking for dates, you say?
But you refer to numbers between 1 and 6, and 0's
So where are the dates?

Maybe you should elaborate more, and explain how each row is utilised, so
that we can know what it is that you are trying to achieve?

--
HTH

Kassie

Replace xxx with hotmail


"Kobus" wrote:

I have various rows with numbers entered randomly with 0's inbetween. The
first row has are represents calender dates. I need to find the date of the
first number entry and the date of the last number entry larger than 0. If
of any help the numbers entered are from 1 to 6. These are randomly entered.

address of first and last number in a row
 

Thanks, I should read up on array functions a bit more. I battled with this
one combining a major amount of INDEX, MATCH, COUNTIF, ADDRESS and more. Got
it to find the first number but then failed finding the last.

"T. Valko" wrote:

With dates in row 1 an data in row 2...

For the leftmost (first) number 0...

Array entered** :

=INDEX(A1:J1,MATCH(TRUE,A2:J20,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

For the rightmost (last) number 0...

=LOOKUP(1E100,1/A2:J2,A1:J1)

--
Biff
Microsoft Excel MVP


"Kobus" wrote in message
...
I have various rows with numbers entered randomly with 0's inbetween. The
first row has are represents calender dates. I need to find the date of
the
first number entry and the date of the last number entry larger than 0.
If
of any help the numbers entered are from 1 to 6. These are randomly
entered.

address of first and last number in a row
 

After I read the question it did sound as though I had to better explain but
(I don't know how) T.Valko understood and his/her solution solved my problem.
Row 1 are dates (headings) the subsequent rows are random numbers in a row
ranging from 1 to 6 (the amount of items delivered on that specific day).

"Kassie" wrote:

Hi Kobus,

Your explanation is rather confusing! You are looking for dates, you say?
But you refer to numbers between 1 and 6, and 0's
So where are the dates?

Maybe you should elaborate more, and explain how each row is utilised, so
that we can know what it is that you are trying to achieve?

--
HTH

Kassie

Replace xxx with hotmail


"Kobus" wrote:

I have various rows with numbers entered randomly with 0's inbetween. The
first row has are represents calender dates. I need to find the date of the
first number entry and the date of the last number entry larger than 0. If
of any help the numbers entered are from 1 to 6. These are randomly entered.

address of first and last number in a row
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Kobus" wrote in message
...
Thanks, I should read up on array functions a bit more. I battled with
this
one combining a major amount of INDEX, MATCH, COUNTIF, ADDRESS and more.
Got
it to find the first number but then failed finding the last.

"T. Valko" wrote:

With dates in row 1 an data in row 2...

For the leftmost (first) number 0...

Array entered** :

=INDEX(A1:J1,MATCH(TRUE,A2:J20,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Format as Date

For the rightmost (last) number 0...

=LOOKUP(1E100,1/A2:J2,A1:J1)

--
Biff
Microsoft Excel MVP


"Kobus" wrote in message
...
I have various rows with numbers entered randomly with 0's inbetween.
The
first row has are represents calender dates. I need to find the date
of
the
first number entry and the date of the last number entry larger than 0.
If
of any help the numbers entered are from 1 to 6. These are randomly
entered.