How to eliminate blank spaces

[EduPAz]

Hi,

Does anyone knows how to do this?

I have the fallowing information:

FF

GG

"empty cell"

"empty cell"

PP

<tbody>
</tbody>

What is the formula that allow me to copy those values in sheet2 so I can get this:

FF

GG

PP

<tbody>
</tbody>

Thanks!
Edu

 

[Scott Huish]

Maybe:
=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW(Sheet1!$A$1:$A$1000)),ROW(A1))),"")
Confirm with CTRL-SHIFT-ENTER rather than just Enter.
Copy down.

 

[EduPAz]

Hi Scott,

What happen is the situation is this:

"empty cell"
"empty cell"
A
B
C

In this situation the formula doesn't work. I tried to change in the formula the starting point (a1) to a3. It didn't work

 

[Scott Huish]

Why can't you just leave it as is?

Excel 2010
	A
1
2
3	A
4	B
5	C
Sheet1

Excel 2010
	A
1	A
2	B
3	C
4
5
6
Sheet2

Cell Formulas
Range	Formula
A1	{=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW(Sheet1!$A$1:$A$1000)),ROW(A1))),"")}
A2	{=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW(Sheet1!$A$1:$A$1000)),ROW(A2))),"")}
A3	{=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW(Sheet1!$A$1:$A$1000)),ROW(A3))),"")}
A4	{=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW(Sheet1!$A$1:$A$1000)),ROW(A4))),"")}
A5	{=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW(Sheet1!$A$1:$A$1000)),ROW(A5))),"")}
A6	{=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW(Sheet1!$A$1:$A$1000)),ROW(A6))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.