Formula that returns only values of cells and doesnt return blank/NA values

Hey all,

I am in the process of building a logic template in excel. I am trying to make an "Output" tab because the current tab has a multiple blank rows between information, I want it to plug into a format on the next pages so I can send an email easily. I have multiple tables on one sheet so I cant sort/filter the tables.

For example:
A B C D E
First Name Last Name SS Hire Date Term Date
(blank)
(blank)
John Smith 12346 1/1/16 6/1/16
(blank)
(blank)
Billy Joseph 4567 5/1/16 7/1/16
(blank)

I am looking for

First Name Last Name SS Hire Date Term Date
John Smith 123456 1/1/16 6/1/16
Billy Joseph 4567 5/1/16 7/1/16


Any suggestions?

Originally Posted by pageandrewr1

Hey all,

I am in the process of building a logic template in excel. I am trying to make an "Output" tab because the current tab has a multiple blank rows between information, I want it to plug into a format on the next pages so I can send an email easily. I have multiple tables on one sheet so I cant sort/filter the tables.

For example:
A B C D E
First Name Last Name SS Hire Date Term Date
(blank)
(blank)
John Smith 12346 1/1/16 6/1/16
(blank)
(blank)
Billy Joseph 4567 5/1/16 7/1/16
(blank)

I am looking for

First Name Last Name SS Hire Date Term Date
John Smith 123456 1/1/16 6/1/16
Billy Joseph 4567 5/1/16 7/1/16


Any suggestions?

Step 1: Select column A
Ashampoo_Snap_2016.11.08_20h22m27s_001_.jpg
Step 2: Home/Find & select/Go to Special...
Step 3: Tick Blanks/Ok
Ashampoo_Snap_2016.11.08_20h25m50s_001_.jpg
Step 4: Home/Delete/Delete Sheet Rows
Ashampoo_Snap_2016.11.08_20h27m20s_002_.jpg
Done!

Unfortunately I cant upload anything right now.. Tamthat, that doesnt work because I have multiple charts (2000+ lines) and that will end up deleting information.

Excelisfun009 here is a better example

A
Name
BLANK
ANDREW
BLANK
BLANK
BLANK
BILL
BLANK
BLANK
BLANK
JOHN

I want the output formula to SKIP the blanks and returns cells with information in them
A
Name
Andrew
Bill
John

Thanks, looks good.