how do i dynamically allot rows in excel

[bsn]

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..

 

[John Bundy]

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.

 

[bobsn]

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

 

[John Bundy]

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.