Removing gaps in a series of values

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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
FGHI
11
1211
1345
1445k
152
16k55
1726
187
1955
206
21
227
Sheet3
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,855
Hi babycody

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

Code:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,470
Members
409,884
Latest member
Msinmath
Top