Copying values but skipping empty cells
 

I'm having problem while calculating streaks. I know how it's done, but
I have empty cells in the middle which prevents me getting the streaks
right.

What I'd like to do, is to copy all the numbers to another sheet losing
all empty cells from the middle.

Here's the example. At left side there numbers and A6-A8 are empty
cells. At right side is the example how I would like it to look. So
that A6-A8 from left side would not be copied and cells with numbers
moved up to join first group.


Code:
--------------------
A | A
1 3 | 1 3
2 2 | 2 2
3 5 | 3 5
4 2 | 4 2
5 0 | 5 0
6 | 6 2
7 | 7 1
8 | 8 4
9 2 | 9 2
10 1 | 10 2
11 4 | 11 3
12 2 | 12 1
13 2 | 13
14 3 | 14
15 1 | 15
--------------------


I couldn't find any function to do this, is there an easy way?

Thanks in advance, again :)


--
Handyy
------------------------------------------------------------------------
Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958
View this thread: http://www.excelforum.com/showthread...hreadid=510298

Copying values but skipping empty cells
 

One play, using non-array formulas ..

Source data in A1 down

In B1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,ROW(A1))))

In C1: =IF(A1="","",ROW())

Select B1:C1, copy down to say, C100,
to cover the max expected extent of data in col A

Col B will return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Handyy" wrote in
message ...

I'm having problem while calculating streaks. I know how it's done, but
I have empty cells in the middle which prevents me getting the streaks
right.

What I'd like to do, is to copy all the numbers to another sheet losing
all empty cells from the middle.

Here's the example. At left side there numbers and A6-A8 are empty
cells. At right side is the example how I would like it to look. So
that A6-A8 from left side would not be copied and cells with numbers
moved up to join first group.


Code:
--------------------
A | A
1 3 | 1 3
2 2 | 2 2
3 5 | 3 5
4 2 | 4 2
5 0 | 5 0
6 | 6 2
7 | 7 1
8 | 8 4
9 2 | 9 2
10 1 | 10 2
11 4 | 11 3
12 2 | 12 1
13 2 | 13
14 3 | 14
15 1 | 15
--------------------


I couldn't find any function to do this, is there an easy way?

Thanks in advance, again :)


--
Handyy
------------------------------------------------------------------------
Handyy's Profile:
http://www.excelforum.com/member.php...o&userid=30958
View this thread: http://www.excelforum.com/showthread...hreadid=510298