If you want the results dynamic in the other sheet,
here's one play using non-array formulas ..

Assume source data in Sheet1, cols A & B,
data from row2 down to a max expected row100 (say)

In Sheet2,

Put in A2:
=IF(ROW(A1)COUNT($C:$C),"",INDEX(Sheet1!A:A,MATCH (SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2

Put in C2:
=IF(Sheet1!A2="","",ROW())
(Leave C1 empty)

Select A2:C2, copy down to C100
(just copy down to cover the max expected data range in Sheet1)

Cols A and B will return the required results, all neatly bunched at the top
(Hide away the criteria col C, if necess.)

Note that the criteria in col C above simply checks/focuses on whether
Sheet1's col A is empty. If Sheet1's col A is empty on that row, it's deemed
the row is empty.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
I am trying to create a sheet that will read 2 columns of another sheet
that has empty spaces between the data (Rows) and I want to re-create
the data in a new sheet that will re-fill the columns without the
spaces.. Thanks

e.g. :

_COL_A_______COL_B_

apples 50
grapes 70

bannanas 40

peaches 80

TO:

_COL_A_______COL_B_

apples 50
grapes 70
bannanas 40
peaches 80


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376