Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically sized table compiled from unsorted rows with blank entries
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically sized table compiled from unsorted rows with blank entries
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 On Jan 30, 9:49*pm, Ezra wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically sized table compiled from unsorted rows with blank entries
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help? with extracting unsorted data from a worksheet, no blank lin | Excel Worksheet Functions | |||
Pivot Table blank rows | Excel Discussion (Misc queries) | |||
in a pivot table, can the average include blank entries? | Excel Discussion (Misc queries) | |||
How do I add a larger sized, blank every other row in a spread she | Excel Discussion (Misc queries) | |||
Printing a legal sized document on letter sized paper | Excel Programming |