How to do a door worksheet schedule by Type?

[Christiaan]

I've created a symbol which inlcudes the data: unit (flat) number and door number (allowing for individual identification of each door) and door type, and then created a worksheet schedule based on this data.

With 500 doors (and hence rows), however, this has made my atempt at a schedule unruly, and I probably need to base my worksheet rows on door Type as opposed to each individual door.

Any advice on strategy?

How do I go about editing my worksheet schedule to only have the same number of rows as there are door Types? What about if I want to include multiple door and unit numbers in individual cells (for each Type/row)?

[Petri]

Sort and Sum by Type.

Multiple instances (door no, unit no) in a cell (row) can't, as far as I know, be done with worksheet functions. You would get something like that with FileMaker Pro - kind of columns for each instance, by using multi-column "body" and a "subsummary" for each type.

In fact, for this kind of complexity, an external database might well start to make sense.

The other possibility is a VS program that processes each door type and creates an "array" of instances. The whole shebang is then written into a clean, "dumb" worksheet. Not hugely demanding - maybe 20 to 30 lines of code.

[Christiaan]

Poos. That's not the answer I was hoping for.

Just to be clear, forgetting the door and unit numbers, is there any way to have one row for each Type? Say I have 4 doors, 2 of which are Type A and 2 of which are Type B. Can I set up a worksheet that simply has two rows (A and B) rather than 4 rows (A, A, B and B).

[Petri]

Yes, by using Sum.

[Robert Anderson]

Christiaan, you can do this:

1. Select in the row heading of your door database range in the worksheet. The little "summarize and sort" icons at the top of the worksheet window should enable.

2. Drag the summarize icon to the column (e.g. Type) that you want to summarize by. This will summarize the database range on that column value.

3. Looking at the summarized worksheet, you will find the following things: (a) numerical fields total up; (b) text fields that are undifferentiated display as text; and © text fields that are differentiated display as a dash (--). If you want to display sub-summarization (i.e. if you want to see a single value (not a total) or if you want to see the various text values broken out), then drag a sum icon to the column of interest. You are limited to three columns summed per WS.

HTH, Robert

[Petri]

I have found the "dashes" quite useful: they may indicate that the classification/typification has gone wrong.

Eg: if my "door types" are supposed to be really identical, but there are dashes in "hardware" column, there's still work to do. (Not that anything like this would ever happen to me...)

[Christiaan]

If you want to display sub-summarization (i.e. if you want to see a single value (not a total) or if you want to see the various text values broken out), then drag a sum icon to the column of interest. You are limited to three columns summed per WS.

Thanks Robert. I think I'm just going to break down schedule into floor levels (there're six floors). I think one problem I've created for myself is separating the door number and flat number. Maybe I should have made this one piece of data (i.e. 01D1 instead of 01| D1).

Putting aside the above, what I would have liked is something like the following:

Door number | Door Type

D1,D5,D7,etc. A

D10,D15,D17,etc. B

D2,D18,D40,etc. C

Is it possible to do this?

[Petri]

Not - except with a script.