Exclude #N/A values and Return Numeric values to consecutive cells in Single Row

[Sam via OfficeKB.com]

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

 

[T. Valko]

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>

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you for reply. Your formula representation looks suitable. Please Post.

When you say:

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

 

[T. Valko]

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:$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)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Sam via OfficeKB.com]

Hi Biff,

That's Brilliant! Your last formula provided the required result. Thank you
very much for your time and help.

Assume you want the results starting cell in C53:

Array entered** :

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

 

[T. Valko]

You're welcome. Thanks for the feedback!