Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row
Hi All,
I have an array of data in a single row that includes numeric and #N/A values. I would like to return only the numeric values of the formula based array to consecutive cells in a single row; without: empty text, blanks or #N/A values in any of the returned cells. Sample Data: Row 50, column "C" to column "AG". Column C, D, E, F, G, H, I, J Row50 #N/A, #N/A, #N/A, 104, #N/A, 150, 179 #N/A Expected Results: 104, 150, 179 Expected results returned to consecutive cells in a single row (no blanks, no empty text, no #N/A). Cheers, Sam -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row
When you say:
no blanks, no empty text Expected Results: 104, 150, 179 Using a formula the results would be: | 104 | 150 | 179 | "" | "" | "" | "" | "" | Where "" is a blank cell. The cell will contain the formula but return a blank. If that's unacceptable then you'll need a macro to do this and you should post in the programming ng. Like I said before, Sam's posts are *always* the most complicated posts, bar none! <g -- Biff Microsoft Excel MVP "Sam via OfficeKB.com" <u4102@uwe wrote in message news:7f7153aa1924e@uwe... Hi All, I have an array of data in a single row that includes numeric and #N/A values. I would like to return only the numeric values of the formula based array to consecutive cells in a single row; without: empty text, blanks or #N/A values in any of the returned cells. Sample Data: Row 50, column "C" to column "AG". Column C, D, E, F, G, H, I, J Row50 #N/A, #N/A, #N/A, 104, #N/A, 150, 179 #N/A Expected Results: 104, 150, 179 Expected results returned to consecutive cells in a single row (no blanks, no empty text, no #N/A). Cheers, Sam -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row
Hi Biff,
Thank you for reply. Your formula representation looks suitable. Please Post. T. Valko wrote: When you say: no blanks, no empty text Expected Results: 104, 150, 179 Should have said, "no blanks, no empty text, between the returned numeric values". Using a formula the results would be: | 104 | 150 | 179 | "" | "" | "" | "" | "" | Where "" is a blank cell. The cell will contain the formula but return a blank. That looks fine, if no blanks are actually between the returned numeric values. If that's unacceptable then you'll need a macro to do this and you should post in the programming ng. Your formula representation is suitable. Like I said before, Sam's posts are *always* the most complicated posts, bar none! <g <bg Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row
Assume you want the results starting cell in C53:
Array entered** : =IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),INDEX($C50 :$J50,SMALL(IF(ISNUMBER($C50:$J50),COLUMN($C50:$J5 0)-MIN(COLUMN($C50:$J50))+1),COLUMNS($C53:C53))),"") Copied across. If the values are in ascending order as is depicted in your sample data then you can use a less complicated array formula** : =IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),SMALL(IF(I SNUMBER($C50:$J50),$C50:$J50),COLUMNS($C53:C53))," ") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sam via OfficeKB.com" <u4102@uwe wrote in message news:7f71ec39d8399@uwe... Hi Biff, Thank you for reply. Your formula representation looks suitable. Please Post. T. Valko wrote: When you say: no blanks, no empty text Expected Results: 104, 150, 179 Should have said, "no blanks, no empty text, between the returned numeric values". Using a formula the results would be: | 104 | 150 | 179 | "" | "" | "" | "" | "" | Where "" is a blank cell. The cell will contain the formula but return a blank. That looks fine, if no blanks are actually between the returned numeric values. If that's unacceptable then you'll need a macro to do this and you should post in the programming ng. Your formula representation is suitable. Like I said before, Sam's posts are *always* the most complicated posts, bar none! <g <bg Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row
Hi Biff,
That's Brilliant! Your last formula provided the required result. Thank you very much for your time and help. T. Valko wrote: Assume you want the results starting cell in C53: Array entered** : =IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),INDEX($C5 0:$J50,SMALL(IF(ISNUMBER($C50:$J50),COLUMN($C50:$J 50)-MIN(COLUMN($C50:$J50))+1),COLUMNS($C53:C53))),"") Copied across. If the values are in ascending order as is depicted in your sample data then you can use a less complicated array formula** : =IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),SMALL(IF( ISNUMBER($C50:$J50),$C50:$J50),COLUMNS($C53:C53)), "") Even though my values were not in ascending order, the above, less complicated formula returned my numeric values in ascending order. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Cheers, Sam -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Sam via OfficeKB.com" <u4102@uwe wrote in message news:7f72b56b9744e@uwe... Hi Biff, That's Brilliant! Your last formula provided the required result. Thank you very much for your time and help. T. Valko wrote: Assume you want the results starting cell in C53: Array entered** : =IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),INDEX($C 50:$J50,SMALL(IF(ISNUMBER($C50:$J50),COLUMN($C50:$ J50)-MIN(COLUMN($C50:$J50))+1),COLUMNS($C53:C53))),"") Copied across. If the values are in ascending order as is depicted in your sample data then you can use a less complicated array formula** : =IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),SMALL(IF (ISNUMBER($C50:$J50),$C50:$J50),COLUMNS($C53:C53)) ,"") Even though my values were not in ascending order, the above, less complicated formula returned my numeric values in ascending order. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Cheers, Sam -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Unique Consecutive Duplicate Values across Single Row | Excel Worksheet Functions | |||
Return Unique Duplicate Numeric Values across Single Row | Excel Worksheet Functions | |||
Return Numeric Labels that have Values =4 across Single Row | Excel Worksheet Functions | |||
Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
Return Single Instance of Numeric Values from a Column | Excel Worksheet Functions |