Removing gaps in a series of values

[babycody]

As you can see the first column has blank rows of data. I need a formula that will create a series without blanks like I have in the second column. The data in the first column will constantly change so a trick like filtering, and then selecting visible cells only won't work in this case. There will never be more than one empty row between the values in the first column. I really need a formula to make this work. Thanks.

Barcode project.xls
	F	G	H	I
11
12	1	1
13		45
14	45	k
15		2
16	k	55
17	2	6
18		7
19	55
20	6
21
22	7
Sheet3

 

[babycody]

Nevermind, I finally found this topic hear: http://www.cpearson.com/excel/noblanks.htm Thanks for any interest.

 

[pgc01]

Hi babycody

Using your example with data in F12:F24, write in G12

=IF(ROW()-ROW($F$12)>=COUNTA($F$12:$F$24),"",INDEX($F:$F,SMALL(IF($F$12:$F$24<>"",ROW($F$12:$F$24)),ROW()-ROW($F$12)+1)))

Confirm with CTRL+SHIFT+ENTER.
Copy down till G24

HTH
PGC