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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,141,740
Messages
5,708,235
Members
421,553
Latest member
Geeyj

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