Dynamic allocation of rows

[bobsn]

Hello all

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

Thanks for your suggestions and time.

 

[paul.robinson]

Hi
Look up Pivot Tables in help - that may be what you are looking for.
Very easy to set up and use and once you get used to them, you'll
never leave home without one.
regards
Paul

 

[bobsn]

No. Pivot table is not I am looking for...


Lets say I have

Product Value
A 2
B 3
C 12
D 4
E 78
#N/A #N/A
#N/A #N/A
#N/A #N/A

I would like to see in my next step..

Product Value
A 2
B 3
C 12
D 4
E 78

But the first table keeps changing depending on some criterion... It
may have 10 rows of which 2 have data and the rest is #N/A's... So my
child table ie, the second one here should have only 2 rows.

Product Value
A 2
B 3
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

In this case my child table should be

Product Value
A 2
B 3

Hope this is clear... Thanks.

 

[JLGWhiz]

Why not use autofilter for everything not equal to #N/A?

 

[bobsn]

I tried that already. The issue is there are more calculations on the
value column (in example) once I have achieved this. Also it is not an
issue of displaying the #N/A's. I am looking to extract information
from the first table with values and put them in the second table.

-Thanks