Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste special | values should work with merged cells | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
how do populate empty cells with the contents of populated cells . | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Matrix multiplication with empty cells | Excel Worksheet Functions |