Dynamically sized table compiled from unsorted rows with blank entries

[Ezra]

Greetings-
I have spent the better part of the day trying to figure out the
following - I now the title doesn't describe the problem well.

What I am trying to do, is make a "summary table" that compiles
information from several categories into one, but to allow for the
table to dynamically update (data and size) as the data change.

Here is a generic example of what I am trying to do:
* Consider 4 categories (blue, green, red and yellow). Each category
has a variable number of data in it - no more than a 100 per category.
In the data entry sheet, I have apportioned 100 rows for each category
(the blues are int the 100s, the greens in the 200s, the reds in the
300s etc). Any unused rows, are left blank, as they may be populated
in the future.

In other words:
RowNum *Category* *Points*
2 Blue 11
3 Blue 32
4 Blue 26
5 (Blank)
6 (Blank)
....
99 (Blank)
100(Blank)
101 Green 9
102 Green 45
....
199(Blank)
200(Blank)
201 Red 14
202 Red 39
and so on and so forth.


What I am trying to do is create a dynamically contractible/expandable
table that shows:
*Category* *Points*
Blue 11
Blue 32
Blue 26
Green 9
Green 45
Red 14
Red 39

but that can accommodate the later addition of new category data.
Ideally, the table size would be defined (on the fly) by the count of
non-blank rows in each category, and would present the data
contiguously.

Any thoughts would be much appreciated.
Ezra

 

[Pete_UK]

You could apply Autofilter to one of the columns and choose NonBlanks
to hide them. This is not dynamic, though - you will need to re-apply
the filter if the data changes.

Hope this helps.

Pete

 

[Ezra]

Thank you Pete. I am trying to see if there is a way to do so in a
more automated fashion, and also without using a pivot table; it's
part of a larger automation process, so I can't really insert any
manual steps in there without sacrificing the rest of the automation.
Ezra