The problem : the user generates an input list (col A numbered by row 1,2,3
etc, col B = entry time, col C = leave time). The numbered rows refer to kids
(entering school) but not every kid comes in. So, the list has completed rows
as well as blank rows:
1 8:00 15:00
2
3 9:00 11:00
4

The user wants to generate a list WITHOUT the empty rows. Obviously easily
done with Visual Basic, but I question if this can be done with array
formula's.

Starting with Ingrids advise on
http://users.telenet.be/ingrid/excel/matrix.htm#tekst (thanks !) I created
the array
=ROW(INDIRECT("1:"&COUNT(A1:A4))) which gives me {1,2,3,4}
=OFFSET($B$1,ROW(INDIRECT("1:"&COUNT(A1:A4)))-1,0) subsequently gives {8:00,
0, 9:00, 0}
but I can't get rid of the zero's in between.....

Who has clever idea's???

RDWJ

Take a look here for this sort of thing:
http://www.cpearson.com/excel/noblanks.htm

"rdwj" wrote in message
...
The problem : the user generates an input list (col A numbered by row
1,2,3
etc, col B = entry time, col C = leave time). The numbered rows refer to
kids
(entering school) but not every kid comes in. So, the list has completed
rows
as well as blank rows:
1 8:00 15:00
2
3 9:00 11:00
4

The user wants to generate a list WITHOUT the empty rows. Obviously easily
done with Visual Basic, but I question if this can be done with array
formula's.

Starting with Ingrids advise on
http://users.telenet.be/ingrid/excel/matrix.htm#tekst (thanks !) I
created
the array
=ROW(INDIRECT("1:"&COUNT(A1:A4))) which gives me {1,2,3,4}
=OFFSET($B$1,ROW(INDIRECT("1:"&COUNT(A1:A4)))-1,0) subsequently gives
{8:00,
0, 9:00, 0}
but I can't get rid of the zero's in between.....

Who has clever idea's???

RDWJ

Stephen - thanks for the tip, took me a while to understand the full formula
but got it sorted & managed to adjust to suit the need. Excellent. Txs.

"Stephen" wrote:

Take a look here for this sort of thing:
http://www.cpearson.com/excel/noblanks.htm

"rdwj" wrote in message
...
The problem : the user generates an input list (col A numbered by row
1,2,3
etc, col B = entry time, col C = leave time). The numbered rows refer to
kids
(entering school) but not every kid comes in. So, the list has completed
rows
as well as blank rows:
1 8:00 15:00
2
3 9:00 11:00
4

The user wants to generate a list WITHOUT the empty rows. Obviously easily
done with Visual Basic, but I question if this can be done with array
formula's.

Starting with Ingrids advise on
http://users.telenet.be/ingrid/excel/matrix.htm#tekst (thanks !) I
created
the array
=ROW(INDIRECT("1:"&COUNT(A1:A4))) which gives me {1,2,3,4}
=OFFSET($B$1,ROW(INDIRECT("1:"&COUNT(A1:A4)))-1,0) subsequently gives
{8:00,
0, 9:00, 0}
but I can't get rid of the zero's in between.....

Who has clever idea's???

RDWJ