# Consolidating data in a table into 1 list

#### urgharda

##### New Member
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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### fairwinds

##### MrExcel MVP
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
OPQRS
3a00
4s00
5d00
6f00
7g00
80j0
90k0
1000j
1100j
1200k
1300g
1400f
15d00
16000
17df0
18000
19000
20000
21
22
23
24
25a
26s
27d
28f
29g
30j
31k
32j
33j
34k
35g
36f
37d
38d
39f
400
410
420
Sheet1

#### urgharda

##### New Member
fairwinds,
It works perfectly, thank you for your help.

Replies
0
Views
360
Replies
0
Views
287
Replies
0
Views
412
Replies
1
Views
616
Replies
5
Views
383

1,181,790
Messages
5,932,062
Members
436,816
Latest member
Composh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back