+ Reply to Thread
Results 1 to 5 of 5

Listing values while ignoring blanks

  1. #1
    Registered User
    Join Date
    07-03-2008
    Location
    Scotland
    Posts
    58

    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. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    A quick way is to select col B, copy it to col C, select the whole column and sort ascending.

  3. #3
    Registered User
    Join Date
    07-03-2008
    Location
    Scotland
    Posts
    58
    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. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    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. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    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!
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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