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

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

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

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

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

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