I have a spreadsheet with items listed from top to bottom on the left, with
rooms going across the top. If the item is required in teh room I put the
qty. This means teh chart is very large and not every item is in every room.

I want to be able to 'filter' this chart so it produces a room by room
breakdown on one long list....

i.e.
Room 1 Room 2 Room 3
Chair 1 1
Table 1
Stool 1 1
Bin 1 1 1

.....would be produced as...

Room 1
Chair - 1
Table - 1
Stool - 1
Bin - 1

Room 2
Stool - 1
Bin - 1

Room 3
Chair - 1
Bin - 1


The actual chart is as wide as excel will do and about 500 hundred items
long, just to give you a scale on it.

I can send over the file if you fancy a challenge?