| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return All Non-Blank Cells
I have the following text in column A rows 17 through 26
17 PY02 18 19 PY04 20 PY05 21 PY06 22 PY07 23 PY08 24 PY09 25 PY10 26 PY11 Then I use this formula =IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"") in A43 and copy down to A52. I'm expecting it to return the following in Column A rows 43:52 43 PY02 44 PY04 45 PY05 46 PY06 47 PY07 48 PY08 49 PY09 50 PY10 51 PY11 52 But I'm getting this instead: 43 PY02 44 PY05 45 PY06 46 PY08 47 PY10 48 49 50 51 52 I don't know why it's skipping PY04, PY07, PY09, and PY11. Any help is greatly appreciated! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return All Non-Blank Cells
Try something like this: A43: =IF(COUNTA($A$17:$A$26)=ROWS($43:43),INDEX($A$17: $A$26,SMALL((ISBLANK($A$17:$A$26)*10^99+ROW($A$17: $A$26)-ROW($A$17)+1),ROWS($43:43))),"") Copy that formula down through A52 Note: in case text wrap impacts the display, there are NO spaces in that formula. Using your sample data, that formula returns these values in A43:A52 PY02 PY04 PY05 PY06 PY07 PY08 PY09 PY10 PY11 (blank) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "shorticake" wrote in message ... I have the following text in column A rows 17 through 26 17 PY02 18 19 PY04 20 PY05 21 PY06 22 PY07 23 PY08 24 PY09 25 PY10 26 PY11 Then I use this formula =IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"") in A43 and copy down to A52. I'm expecting it to return the following in Column A rows 43:52 43 PY02 44 PY04 45 PY05 46 PY06 47 PY07 48 PY08 49 PY09 50 PY10 51 PY11 52 But I'm getting this instead: 43 PY02 44 PY05 45 PY06 46 PY08 47 PY10 48 49 50 51 52 I don't know why it's skipping PY04, PY07, PY09, and PY11. Any help is greatly appreciated! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return All Non-Blank Cells
The formula I posted needed one more tweak....
Try this regular formula: =IF(COUNTA($A$17:$A$26)=ROWS($43:43),INDEX($A$17: $A$26,SMALL(INDEX((ISBLANK($A$17:$A$26)*10^99+ROW( $A$17:$A$26)-ROW($A$17)+1),0),ROWS($43:43))),"") Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... Try something like this: A43: =IF(COUNTA($A$17:$A$26)=ROWS($43:43),INDEX($A$17: $A$26,SMALL((ISBLANK($A$17:$A$26)*10^99+ROW($A$17: $A$26)-ROW($A$17)+1),ROWS($43:43))),"") Copy that formula down through A52 Note: in case text wrap impacts the display, there are NO spaces in that formula. Using your sample data, that formula returns these values in A43:A52 PY02 PY04 PY05 PY06 PY07 PY08 PY09 PY10 PY11 (blank) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "shorticake" wrote in message ... I have the following text in column A rows 17 through 26 17 PY02 18 19 PY04 20 PY05 21 PY06 22 PY07 23 PY08 24 PY09 25 PY10 26 PY11 Then I use this formula =IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"") in A43 and copy down to A52. I'm expecting it to return the following in Column A rows 43:52 43 PY02 44 PY04 45 PY05 46 PY06 47 PY07 48 PY08 49 PY09 50 PY10 51 PY11 52 But I'm getting this instead: 43 PY02 44 PY05 45 PY06 46 PY08 47 PY10 48 49 50 51 52 I don't know why it's skipping PY04, PY07, PY09, and PY11. Any help is greatly appreciated! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return All Non-Blank Cells
Try it like this (array entered):
=IF(ROWS(A$43:A43)<=COUNTA(A$17:A$26),INDEX(A$17:A $26,SMALL(IF(A$17:A$26<"",ROW(A$17:A$26)-MIN(ROW(A$17))+1),ROWS(A$43:A43))),"") -- Biff Microsoft Excel MVP "shorticake" wrote in message ... I have the following text in column A rows 17 through 26 17 PY02 18 19 PY04 20 PY05 21 PY06 22 PY07 23 PY08 24 PY09 25 PY10 26 PY11 Then I use this formula =IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"") in A43 and copy down to A52. I'm expecting it to return the following in Column A rows 43:52 43 PY02 44 PY04 45 PY05 46 PY06 47 PY07 48 PY08 49 PY09 50 PY10 51 PY11 52 But I'm getting this instead: 43 PY02 44 PY05 45 PY06 46 PY08 47 PY10 48 49 50 51 52 I don't know why it's skipping PY04, PY07, PY09, and PY11. Any help is greatly appreciated! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return All Non-Blank Cells
Thanks so much!!
"T. Valko" wrote: Try it like this (array entered): =IF(ROWS(A$43:A43)<=COUNTA(A$17:A$26),INDEX(A$17:A $26,SMALL(IF(A$17:A$26<"",ROW(A$17:A$26)-MIN(ROW(A$17))+1),ROWS(A$43:A43))),"") -- Biff Microsoft Excel MVP "shorticake" wrote in message ... I have the following text in column A rows 17 through 26 17 PY02 18 19 PY04 20 PY05 21 PY06 22 PY07 23 PY08 24 PY09 25 PY10 26 PY11 Then I use this formula =IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"") in A43 and copy down to A52. I'm expecting it to return the following in Column A rows 43:52 43 PY02 44 PY04 45 PY05 46 PY06 47 PY07 48 PY08 49 PY09 50 PY10 51 PY11 52 But I'm getting this instead: 43 PY02 44 PY05 45 PY06 46 PY08 47 PY10 48 49 50 51 52 I don't know why it's skipping PY04, PY07, PY09, and PY11. Any help is greatly appreciated! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return All Non-Blank Cells
I wanted to follow the logic so I tried to run Evaluate Function, but each
time it immediately shutdown excel. Do you know why this is happening? Thanks again! "T. Valko" wrote: Try it like this (array entered): =IF(ROWS(A$43:A43)<=COUNTA(A$17:A$26),INDEX(A$17:A $26,SMALL(IF(A$17:A$26<"",ROW(A$17:A$26)-MIN(ROW(A$17))+1),ROWS(A$43:A43))),"") -- Biff Microsoft Excel MVP "shorticake" wrote in message ... I have the following text in column A rows 17 through 26 17 PY02 18 19 PY04 20 PY05 21 PY06 22 PY07 23 PY08 24 PY09 25 PY10 26 PY11 Then I use this formula =IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"") in A43 and copy down to A52. I'm expecting it to return the following in Column A rows 43:52 43 PY02 44 PY04 45 PY05 46 PY06 47 PY07 48 PY08 49 PY09 50 PY10 51 PY11 52 But I'm getting this instead: 43 PY02 44 PY05 45 PY06 46 PY08 47 PY10 48 49 50 51 52 I don't know why it's skipping PY04, PY07, PY09, and PY11. Any help is greatly appreciated! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return All Non-Blank Cells
I don't know the specific reason so I just call it a "bug".
I've complained about this happening several times! I know this happens in Excel 2002 and Excel 2003. The evaulute formula tool was introduced in Excel 2002. I don't know if this is still available in Excel 2007. It's a very useful tool (when it's not crashing Excel!). It only happens on cetain types of array formulas and usually happens when the process gets to this type of expression: SMALL(IF(A$17:A$26<"" I've learned to recognize what types of formulas will cause this crash so I just avoid evaluating them altogether or, I'll use a minimal range size (no more than 5 cells) if I just need to verify the logic is working correctly. -- Biff Microsoft Excel MVP "shorticake" wrote in message ... I wanted to follow the logic so I tried to run Evaluate Function, but each time it immediately shutdown excel. Do you know why this is happening? Thanks again! "T. Valko" wrote: Try it like this (array entered): =IF(ROWS(A$43:A43)<=COUNTA(A$17:A$26),INDEX(A$17:A $26,SMALL(IF(A$17:A$26<"",ROW(A$17:A$26)-MIN(ROW(A$17))+1),ROWS(A$43:A43))),"") -- Biff Microsoft Excel MVP "shorticake" wrote in message ... I have the following text in column A rows 17 through 26 17 PY02 18 19 PY04 20 PY05 21 PY06 22 PY07 23 PY08 24 PY09 25 PY10 26 PY11 Then I use this formula =IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"") in A43 and copy down to A52. I'm expecting it to return the following in Column A rows 43:52 43 PY02 44 PY04 45 PY05 46 PY06 47 PY07 48 PY08 49 PY09 50 PY10 51 PY11 52 But I'm getting this instead: 43 PY02 44 PY05 45 PY06 46 PY08 47 PY10 48 49 50 51 52 I don't know why it's skipping PY04, PY07, PY09, and PY11. Any help is greatly appreciated! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
| IF function to return values for cells with blank or - in them | Excel Worksheet Functions | |||
| Need Vlookup to return a value of $0.00 in blank cells | Excel Worksheet Functions | |||
| Need Vlookup to return a value of $0.00 in blank cells | Excel Worksheet Functions | |||
| referencing cells that return blank results | Excel Worksheet Functions | |||
Linear Mode
