Consolidating data in a table into 1 list

urgharda

New Member
Joined
Jan 18, 2005
Messages
2
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
 

Forum statistics

Threads
1,148,530
Messages
5,747,234
Members
424,070
Latest member
smanni3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top