List has blanks in it, create new column without the blanks

**simmo86** · 06-25-2009, 05:48 AM

I have a list that imports into excel and it has many blank rows in between the fields, I dont want to touch this data, but on a new sheet I want to have a list of all the values but with no spaces. Therefore everytime I import new data it will automatically give a list in the other sheet without all the blank spaces. Is there a formula where it will show a figure, and if it is a blank cell it will scan down to the next empty cell and then display the value in that cell?

Thanks in advance,

Alex

**martindwilson** · 06-25-2009, 06:48 AM

so i havent got to re invent the wheel lol
its nicely done here
http://www.cpearson.com/excel/noblanks.htm

**simmo86** · 06-25-2009, 07:26 AM

Thats great, thanks very much. I was wondering though, is it possible to alter this so that say for example as the data comes in in column D in sheet 1, I want the list with no blanks to be in column A in sheet 2. I cant see where in the formula I can change the look up location.

Thanks,

Alex

**martindwilson** · 06-25-2009, 07:42 AM

if you create those named ranges it doesnt matter what sheet they are on
so say blanksrange refers to sheet1!d1:d1000 and noblanksrange refers to sheet2!a1:a1000
just put that formula in sheet2 a1 cofirm with CSE and drag down

**simmo86** · 06-25-2009, 08:25 AM

I have tried the original formula in a trial sheet which worked and then i tried the cross reference between sheets and it isnt working

**martindwilson** · 06-25-2009, 09:50 AM

i see why ,it is trying to ref the same sheet it needs to be adjusted to add sheet name where blanks range is
=IF(ROW()-ROW(NOBLANKS)+1>ROWS(BLANKS)-
COUNTBLANK(BLANKS),"",INDIRECT("SHEET1!"&ADDRESS(SMALL(
(IF(BLANKS<>"",ROW(BLANKS),ROW()+ROWS(BLANKS))),
ROW()-ROW(NOBLANKS)+1),COLUMN(BLANKS),4)))

**simmo86** · 06-25-2009, 10:33 AM

Thanks very much! That is fantastic

Alex

**simmo86** · 09-07-2009, 09:49 AM

What if the cells arent blank and they actually have a value of 0 is there anyway that these 0's can be included to be eliminated in the same formula?

**Vic_from_sweden** · 06-15-2016, 09:53 AM

I have an extended question on this.

I have used the formula in the example and for one column it works great.

But I wonder if it is possible to have BlanksRange to cover more than one column?

I cannot add attachments for some reason, but instead of column A going into B without blanks I would like column C and D to go into E without blanks.

/Viktor