I have a 2 columns and 45 rows of data that is a result set of previous
selections. and they are related one to one.

The data in this table changes dynamically based on the previous selections.
So on one selection it could have 6 rows and the rest 39 as N/A. On another
selection it could have 25 rows and the rest 20 being N/A's.

I need to create another table exactly with these columns which have the
valid rows instead on NA's. So in the first case I should be able to see 2
columns and 6 rows only and in the second the two columns and 25 rows only.

I feel based on the count of valid columns, there needs to be allocation of
rows dynamically. I am not aware of functions in excel to do that..

While you can do this, it is probably better to handle the n/a's. for example
if you are using formulas add if(isna(yourcode),"",yourcode) this will put
blanks where the n/a's were.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"bsn" wrote:

> I have a 2 columns and 45 rows of data that is a result set of previous
> selections. and they are related one to one.
>
> The data in this table changes dynamically based on the previous selections.
> So on one selection it could have 6 rows and the rest 39 as N/A. On another
> selection it could have 25 rows and the rest 20 being N/A's.
>
> I need to create another table exactly with these columns which have the
> valid rows instead on NA's. So in the first case I should be able to see 2
> columns and 6 rows only and in the second the two columns and 25 rows only.
>
> I feel based on the count of valid columns, there needs to be allocation of
> rows dynamically. I am not aware of functions in excel to do that..

Thats a good idea but there are calculations based on the second
column after that and that throws me a #VALUE! error

another quick suggestion would to have the new tab vlookup all results and ""
what comes back as N/A. Barring that you will need some code that throws it
into an array then outputs it in the second tab if the value is not N/A.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"bobsn" wrote:

> Thats a good idea but there are calculations based on the second
> column after that and that throws me a #VALUE! error
>
>