Ignoring Blank Values????????

[Excel=Junky]

I have two columns One for DAYS one for NIGHTS I need to ignore the Blank cells and condense the data into a column to copy and paste data to another sheet so it doesnt show the blank cells.

EXAMPLE: This is what it looks like now


01367 Blank
01379 Blank
Blank 02356
01399 Blank
Blank 02346

EXAMPLE:I need it to look like this


01367 02356
01379 02346
01399

I need this to work automatically without having to sort or change the data manually. The data changes daily and everyone is tired of sorting this manually . We transfer this data from another sheet using the IF formula
and in turn the HLOOKUP formula transfers the sheet data to a shift roster one column is days and one is nights. The HLOOKUP formula is counting the blanks and leaves the #NA value it looks like this

EXAMPLE:

01367 #NA
01379 #NA
#NA 02356
01399 #NA
#NA 02346

The data sits in two columns and I am sending the data to two diffrent columns WE are stuck. I have OFFICE XP professional in Windows XP
any help would be appreciated we have tried pivot tables and various functions If there is a sign or value for the IF function to IGNORE false value I believe it might work.

 

[nevillestoke]

Would it work if you brought all of the non-blank cells into the same column?

If this is a viable option, assuming your data starts at A1 and there is always a blank cell in each row of either column A or B you could use the following:


=IF(A1<>"",A1,B1)

and you can even modify further such that the formula remains "hidden" until either column A or B contains a value - using

=IF(A1+B1<=0,"",IF(A1<>"",A1,B1))

Let me know.

 

[goblin]

There is a very easy method to do this. Just delete the blank cells. The easiest way to do this is to select the range where the data is, Edit->Goto->Special->Blanks, and then Edit->Delete, shifting up. For the keyboard addicts, its Ctrl+G, Alt-S, k, Enter, Ctrl+-, Alt-u,Enter

The VBA code to do this, assuming the data is in Columns A and B is:

  Columns("A:B").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

That's pretty automatic :D

Goblin

 

[Excel=Junky]

The formula worked just like the HLOOKUP formula it still leaves the blank spaces on my shift roster In terms if you have three people on days and then one on nights it skips three rows on the nights column and then puts the employee number in the 4th row I need a function that ignores false all together and leaves no value what so ever.
Thank you very much for helping Us out I did learn more about the IF function. Thank you
Excel=Junky

 

[Excel=Junky]

Goblin
Do I need to use VBA to do this. Or the formula COLUMNS. And if the data changes from the input form where i pull the data from will it change from blank to data and let me special paste to the shift roster.

 

[kskinne]

use Goblin's VBA code, as stated in his post. to use it, press ALT+F11 to open the visual basic editor, which can be accessed at Tools, Macro, Visual Basic Editor on the main menu. Click the Insert on the menu and select 'module'. This will open a new module window. Copy and paste Goblin's code there:

Sub RemoveBlanks()
Columns("A:B").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub

Then close the visual basic editor window. To run the code, go to the Tools menu, select Macro, then Macros. Select your code from the list and click 'run'. It should take the data as it is in your spreadsheet right now and change it to return the results that you said you wanted in your original post.

hth
kevin