Consolidating data in a table into 1 list

[urgharda]

Hi, I'm new to the board and fairly new to using excel (please pardon my ignorance). I have run into the following problem on a sheet used to load an airplane.

I have a table (3 columns x 18 rows / range o3:q20) within which there are a maximum of 18 entries (the remaining cells are zeros). What I would like to do is consolidate the entries in the table into a single list. The new list would would be in range s25:s42 (max 18 entries). The criteria is that the list should fill in from the top down, it should be contiguous (no blanks, no zeros) and recalculate every time the table is changed.
I'm stumped and appreciate any help given.
Thanks

 

[fairwinds]

Welcome to the board!

Try enter:

=IF(ROW()-ROW($S$24)>COUNTIF($O$3:$Q$20,"<>0"),0,INDEX($O$3:$Q$20,INT(SMALL(IF(($O$3:$Q$20<>0),--(ROW($O$3:$Q$20)&{1,2,3})),ROW()-ROW($S$24))/10)-ROW($O$2),RIGHT(SMALL(IF(($O$3:$Q$20<>0),--(ROW($O$3:$Q$20)&{1,2,3})),ROW()-ROW($S$24)))))

in S25, confirm it with Ctrl + Shift + enter, then drag it down.

Book1
	O	P	Q	R	S
3	a	0	0
4	s	0	0
5	d	0	0
6	f	0	0
7	g	0	0
8	0	j	0
9	0	k	0
10	0	0	j
11	0	0	j
12	0	0	k
13	0	0	g
14	0	0	f
15	d	0	0
16	0	0	0
17	d	f	0
18	0	0	0
19	0	0	0
20	0	0	0
21
22
23
24
25					a
26					s
27					d
28					f
29					g
30					j
31					k
32					j
33					j
34					k
35					g
36					f
37					d
38					d
39					f
40					0
41					0
42					0
Sheet1

 

[urgharda]

fairwinds,
It works perfectly, thank you for your help.