# Excel list with no blank /empty spaces



## chinster543210 (Mar 22, 2010)

I need to get rid of the blank spaces in a list automatically without a macro, is this possible? This is how list 2 looks now with =IF(B2=20,B2,"")

.............A...................B.......................C...
1..............................LIST..1..............LIST 2
2..........10................BOB............................ 
3..........20................SARAH................SARAH 
4..........30................STEVE......................... 
5..........20................ANN....................ANN... 

I need "LIST 2" to end up looking like this;..............C
1...................................................................LIST 2
2...................................................................SARAH
3...................................................................ANN
I need values in list 1, which change on a regular basis, to go to list 2 without the blank spaces.
Does anyone know how to do this?


----------



## RSpecianjr (Jan 20, 2010)

Hey chinster543210,

There are a few ways to do this, but they are all calculation intensive. If your workbook is very large, that could be an issue.

Without using a pretty complicated array formula, you will need two columns. One to find the row instance (lets put this in E2 and drag it down to the end of your data)


```
=IF(ISTEXT(E1),MATCH(20,A:A,0),MATCH(20,INDIRECT(CONCATENATE("A",E1+1,":A65536")),0)+E1)
```
In D2, lets put:


```
=IF(ISERROR(E2),"",INDEX(B:B,E2,1))
```
and drag it down to the end of your data.

Hope this helps,

Robert Specian Jr.


----------

