Forum Discussion

null null's avatar
null null
Copper Contributor
Sep 13, 2018

Shift cells up if false

Hi,

I'm a novice, so please reply kindly!

 

I have a two sheet excel document where the first sheet has a list of names in column A, and B has a 'y' or 'n' or is blank. The second sheet I've split in to three columns (yes, no, and blank) and have used an if formula to separate the names in to their corresponding columns. I have used =IF(Sheet1!B2="y",Sheet1!A2," ") to do this (swapping "y" for "n" for the no column and to " " for the blank column.)

 

My next goal is to remove or shift the cells up to get rid of the blank spaces in each of the columns on Sheet2 to just show a list of names.

I have tried Find&Select/GoToSpecial and selecting Blank but of course realised that the cell is not actually blank but full of formula. I've tried changing the " " to input a word that I could then find and select but can't work out how to select them all at once to delete them.

I don't want to delete the row as that would delete data from the other two columns that I want to keep.

 

The second goal would be to record doing that as a macro so I can just ctrl+_ to get rid of those cells each time I needed to.

 

I've trawled the internet and am only confusing myself trying to apply other peoples problems to my own. Please help!

 

Thanks

excel
Formulas and Functions
Macros and VBA

4 Replies

Sort By
    • null null's avatar
      null null
      Copper Contributor
      Sep 14, 2018

      Thanks for your response, Phillip.

      I'm not sure I quite understand your images there, with the duplicate names in the list? I want the each list (y, n, blank)  to list the names off with no gaps in between them. I'm also not sure what the second sheet is?

      Sorry if I seem a bit dim, it's all new to me!

       

      • Philip West's avatar
        Philip West
        Steel Contributor
        Sep 14, 2018

        Hia, did you download the workbook? its an excel spread sheet rather than an image.

         

        Ignore the second sheet, that was just some working out i did, didn't mean to include it.

         

        If you look, there is the first table which is names and y/n/blanks and a second table with those sorted into columns without gaps. Which is what you are trying to do i think.

Resources