I need a formula to do this and skip blank cells?

Question

If i have a list of data and it contains blank cells
then i would like to have the same list in another place but ignoring the blank cells
for more clarification please refer to the uploaded screen shots.
Image one: this is how excel normally do it.
http://img571.imageshack.us/img571/6715/24257983.jpg
Image two: this is what i want i need to make a formula and then drag it to automatically place all the data without the blank cells in the original source
http://img402.imageshack.us/img402/8950/80900868.jpg
Please tell me anything but copy and paste or paste special or select visible.
Thanks in advance

Answer 1

METHOD 1: Use this array formula:

http://www.get-digital-help.com/2007/09/16/excel-remove-blank-cells/



METHOD 2: Other way to do it is:

I will give you the example with/without a header/label row

• row 1 are the labels/headers: Your data range goes from A2:A30

• You don’t have labels/headers on row 1: Your data range goes from A1:A30



STEP 1: SELECT THE TEMPORAL WORKING AREA

• Select 2 columns that will be used for temporal calculation. For the example it will be column H and I



STEP 2: ID THE BLANK CELLS

For any blank cell on column A, the equivalent cell on column H will have the value 0 otherwise it will be a 1

Copy this formula to cell:

• H2 (if you have header/label)

=IF(ISBLANK(A1), "0","1")

• H1 (you don't have header/label) =IF(ISBLANK(A1), "0","1")

Copy the above formula to the rest of the cells on column H (up to cell H30)



STEP 3: OBTAIN THE ROW NUMBER FOR NON BLANK CELLS

Copy this formula to cell:

• I2 (if you have header/label) =MATCH( "1", $H$2:$H$30, 0) + 1

• I1 (you don't have header/label) =MATCH( "1",$H$1:$H$30, 0)

Copy the above formula to the rest of the cells on column I (up to cell I30)



STEP 4: CREATE THE NEW LIST WITHOUT BLANK CELLS

Copy this formula to cell:

• B2 (if you have header/label) =IF(I2>30,"", INDIRECT( ADDRESS(I2,1)))

• B1 (you don't have header/label) =IF(I1>30,"", INDIRECT( ADDRESS(I1,1)))

Copy the above formula to the rest of the cells on column B (up to cell B30)



HINT: To get rid of the temporal columns and the formulas on column B:

Select column B, right-click and select COPY, right-click again and select PASTE SPECIAL, from the list mark the option VALUE and click the OK button,. Now you can delete column H and I



That's all

Answer 2

Skip Blank Cells In Excel

Answer 3

What you want can't be done in the manner that you want. As soon as you enter a formula in a cell that has data, that data is over-written by the formula. You can however filter the data in place to ignore blanks. VBA code can accomplish this if you are willing to run macros on your worksheet.



The sample is very small maybe too small and the reasons for doing what you want to do are not known but are important to analyze in order to come up with an optimal solution to your problem.

Answer 4

It depends on if your 3 5 7 9 is your real data, or if its just an example.



If you want to drag down and only have odd number appear, type 3 and 5, highlight both, then drag down and it will fill in only odd numbers.



Otherwise, if you have a lot of data and just want to skip the blank cells, SORT the data A-Z and it will eliminate the blank cells by placing them all at the bottom. The sort button is the "AZ down arrow"

Answer 5

highlight your list including the blank cells

in menu select data / filter / auto-filter

on top of list there will be a drop down list

open the drop down list and select non-blank

now copy and paste

now restore the original list

select the original list if not already selected

in menu select data / filter / auto filter

list is restored back to original and copied without the blanks cells.

Answer 6

In a new column (Z) enter this in cell 1 =IF(A1="","",row())

In another column (AA) enter =SMALL(Z:Z,ROW(A1)) copy both formula down list. When all data in AA correct copy and paste special>values over the top of data in col A