Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non-Array Play to Eliminate Blank Cells
Column A shows:
1.Apple 2.{Space} 3.Banana 4.{Space} 5.Yellow 6.Red How do I make Column B show: 1.Apple 2.Banana 3.Yellow 4.Red I've seen some hints at how to do this with numbers, but can you figure out a solution with text? Hint?: A simple non-array play .. In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non-Array Play to Eliminate Blank Cells
http://www.cpearson.com/excel/noblanks.htm
-- Gary''s Student - gsnu200801 "Peter" wrote: Column A shows: 1.Apple 2.{Space} 3.Banana 4.{Space} 5.Yellow 6.Red How do I make Column B show: 1.Apple 2.Banana 3.Yellow 4.Red I've seen some hints at how to do this with numbers, but can you figure out a solution with text? Hint?: A simple non-array play .. In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non-Array Play to Eliminate Blank Cells
If you don't want to clutter up your file with helper cells try this array
formula** : Assuming {Space} means it's an EMPTY cell. =IF(ROWS(B$1:B1)<=COUNTA(A$1:A$6),INDEX(A$1:A$6,SM ALL(IF(A$1:A$6<"",ROW(A$1:A$6)),ROWS(B$1:B1))-ROW(A$1)+1),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Peter" wrote in message ... Column A shows: 1.Apple 2.{Space} 3.Banana 4.{Space} 5.Yellow 6.Red How do I make Column B show: 1.Apple 2.Banana 3.Yellow 4.Red I've seen some hints at how to do this with numbers, but can you figure out a solution with text? Hint?: A simple non-array play .. In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non-Array Play to Eliminate Blank Cells
Highlight your data hit F5 Special Blanks OK Edit Delete Shift
Cells Up "Peter" wrote: Column A shows: 1.Apple 2.{Space} 3.Banana 4.{Space} 5.Yellow 6.Red How do I make Column B show: 1.Apple 2.Banana 3.Yellow 4.Red I've seen some hints at how to do this with numbers, but can you figure out a solution with text? Hint?: A simple non-array play .. In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non-Array Play to Eliminate Blank Cells
The simple, fast & easy-to-understand non-array method
that you posted works for both numbers and text in the source col A. Should your source data in col A start from any row other than row1*, just a slight adjustment is needed to col C's formula, viz replace ROW() with ROWS($1:1). *Believe this might be what is tripping things up for you over there Eg assume col A's data starts in row3 down You could use: In B3: =IF(A3="","",ROW()) Ensure cells above, ie B1:B2 are left blank In C3: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1)))) Copy B3:C3 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "Peter" wrote: Column A shows: 1.Apple 2.{Space} 3.Banana 4.{Space} 5.Yellow 6.Red How do I make Column B show: 1.Apple 2.Banana 3.Yellow 4.Red I've seen some hints at how to do this with numbers, but can you figure out a solution with text? Hint?: A simple non-array play .. In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non-Array Play to Eliminate Blank Cells
Your solution worked. Thanks a ton!
"Gary''s Student" wrote: http://www.cpearson.com/excel/noblanks.htm -- Gary''s Student - gsnu200801 "Peter" wrote: Column A shows: 1.Apple 2.{Space} 3.Banana 4.{Space} 5.Yellow 6.Red How do I make Column B show: 1.Apple 2.Banana 3.Yellow 4.Red I've seen some hints at how to do this with numbers, but can you figure out a solution with text? Hint?: A simple non-array play .. In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel (Eliminate blank rows) | Excel Worksheet Functions | |||
how to eliminate a space when the field is blank in a merge | Excel Discussion (Misc queries) | |||
how eliminate zeros in blank cells using paste link | Excel Worksheet Functions | |||
Eliminate creating list that returns blank cells | Excel Worksheet Functions | |||
How can I dynamically eliminate blank cells in a given range in E. | Excel Worksheet Functions |