Formula for displaying room assignments

[CTyson]

This is somewhat what I have.

A The following people will be in Rm 5
B John Smith
C Michael Townsend
D The following people will be in Rm 6
E Jennifer Tucker
F Samuel Rodriguez
G The following people will be in Rm 7
H Allison Taylor
J Jonathan Thompson

This goes on for about 25 rooms. If I insert a cell before the names, is there a formula that will check which rooms are being specified and display the room number the people are assigned to?

 

[KOKOSEK]

Put some sample data into excel, describe expected results and attached this file, please.

 

[CTyson]

Room Information Date: 04/18/2019: Rm-5 Rm-5 John Smith Rm-5 Michael Townsend Date: 04/18/2019: Rm-6 Rm-6 Jennifer Tucker Rm-6 Samuel Rodriguez Date: 04/18/2019: Rm-7 Rm-7 Allison Taylor Rm-7 Jonathan Thompson <colgroup><col><col></colgroup><tbody> </tbody>
I'm creating a pivot table and wanting to add the Room column to show me which Room the people will be in. I'm looking for a formula that will search the preceding Room number and assign it accordingly.

<colgroup><col><col></colgroup><tbody>
</tbody>