# Listing values while ignoring blanks

1. ## Listing values while ignoring blanks

Good morning,

I am trying to do something I could swear I have managed in the past, but cannot for the life of me remember how.

In column B I have a list of mainly blanks, but with a few values (text) in between. What I would like to do is show these values in cell C1 downwards, without the blanks.

So:

Column B

Test 1

Test 2

Test 3

Test 4

Column C:
Test 1
Test 2
Test 3
Test 4

Does anybody know which function I should be using for this?

Many thanks.

2. A quick way is to select col B, copy it to col C, select the whole column and sort ascending.

3. That works quite nicely. I'll write a little macro to do that. Problem solved.

Just out of curiosity, is there a formula function that does this as well? I have this nagging feeling I have done something like this before, before I had anything to do with macros.

4. This formula in C1 and down should be confirmed with Ctrl+Shift+Enter. No doubt it can be shortened.

=IF(ROWS(C\$1:C1)<=COUNTA(\$B\$1:\$B\$100),INDEX(\$B\$1:\$B\$100,SMALL(IF(\$B\$1:\$B\$100<>"",ROW(B\$1:B\$100)-ROW(B\$1)+1),ROWS(C\$1:C1))),"")

5. Hi,

Another quick method is to select column B, go to Edit > Go To > Click Special button, click Constants > OK. With the cells highlighted right click copy, move to column C and paste - all the blanks are gone!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1