Reformatting data and consolidating

[lcotner]

I have a list of room numbers (in a column) and next to each room is that
room's type. It is for a hotel so the type is King, Queen, etc.

I would like to take the information that I have and consolidate it into a
table that lists each room type, identifying the room numbers of each room
type.

What I have now:

101 King Left
102 Queen Right
103 Queen Right
104 King Left
105 King Right
106 Queen Left

etc for about 150 rooms. There are about 30 room type possibilities (not
just King and Queen). I would like to have the following output:

King Left 101, 104, 108, 111 (listing all of the rooms that are King Left)

King Right 105, 112, 114, (listing all of the rooms that are King Right)

etc.

The data is extracted from a drawing, and has the possibility of changing,
so I'd like to have the output be formula based and not value based, although
I could run some routine every time the extracted data changes.

All of the above can be in separate cells, thats OK. I've really been
banging my head against the wall, but it may not be possible with standard
worksheet formulas.

Thanks for any insight.

 

[akphidelt]

lcotner, one way of doing this is using a pivot table.

If you highlight all the data... make sure you include a header column, like
room #, and then type.

When creating the pivot table... put the Type of bed in to the Row.

Put the room number as the column headings... then also put the room number
in to the data field.

Click on the data field, right click... then click field settings. And
change it to Count. And then you can select the bed type from the filters and
it will show you the room numbers. Let me know how this goes.

 

[lcotner]

akphidelt,

The pivot table did what I wanted, it listed each room type and room number
like this:

King Left 101 1
102 1
107 1
King Left total 3

King Right 105 1
108 1
King Right total 2

etc.

I don't know enough about pivot tables to make the listing of room numbers
to go o ut to the right instead of filling out down, but when I rearrange the
fields, the room numbers become column headings and the counts become fields
in the table.

I can live with this as it gives me what I need.

The table is set up like this:

Row Labels
"Room Type"
"Room Number"

Values
Count of "Room Number"

Thanks alot for your help!