Finding gaps
 

Hi!

here is the data from A1:R4

1 2 3 4 0 1 2 0 0 1 2 3 4 5 0 1 2 3
1 2 3 4 5 6 7 8 9 0 1 2 0 1 2 3 4 5
1 0 1 0 1 2 3 0 1 2 3 4 5 6 7 0 1 2
0 1 0 1 2 3 4 5 0 1 2 3 0 1 2 0 1 2
1 2 3 4 0 1 2 3 4 5 6 7 8 0 1 2 3 4

I want to first search all zeros in the range A1:R1 and put the left number
of the zero in cell S1. Then search the next zero in the same range and put
the left number of the second zero in cell T1. Do this until we are finished
with checking zeros in the range A1:R1. The result in the range S1:V1 should
be 4 2 0 5

Once finished, go to the next range B1:R1 and do this till the last range
B4:R4

Maxi

Copy this into S1 and press ctrl + shift + enter:

=INDEX($A1:$Q1,SMALL(IF($B1:$R1=0,COLUMN($B1:$R1)) ,COLUMN
()-18)-1)

Fill the formula down to S4. Now fill S1:S4 across until
you receive 4 #NUM! errors in the column.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi!

here is the data from A1:R4

1 2 3 4 0 1 2 0 0 1 2 3 4 5 0 1 2 3
1 2 3 4 5 6 7 8 9 0 1 2 0 1 2 3 4 5
1 0 1 0 1 2 3 0 1 2 3 4 5 6 7 0 1 2
0 1 0 1 2 3 4 5 0 1 2 3 0 1 2 0 1 2
1 2 3 4 0 1 2 3 4 5 6 7 8 0 1 2 3 4

I want to first search all zeros in the range A1:R1 and
put the left number
of the zero in cell S1. Then search the next zero in the
same range and put
the left number of the second zero in cell T1. Do this
until we are finished
with checking zeros in the range A1:R1. The result in
the range S1:V1 should
be 4 2 0 5

Once finished, go to the next range B1:R1 and do this
till the last range
B4:R4

Maxi
.