address of first and last number in a row

  • Thread starter Thread starter Kobus
  • Start date Start date
K

Kobus

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.
 
With dates in row 1 an data in row 2...

For the leftmost (first) number >0...

Array entered** :

=INDEX(A1:J1,MATCH(TRUE,A2:J2>0,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)
 
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
 
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.
 
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).
 
Back
Top