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]

[TABLE="width: 276"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 241"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Room
[/TD]
[TD]Information
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date: 04/18/2019: Rm-5
[/TD]
[/TR]
[TR]
[TD]Rm-5[/TD]
[TD]John Smith[/TD]
[/TR]
[TR]
[TD]Rm-5[/TD]
[TD]Michael Townsend[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date: 04/18/2019: Rm-6[/TD]
[/TR]
[TR]
[TD]Rm-6[/TD]
[TD]Jennifer Tucker[/TD]
[/TR]
[TR]
[TD]Rm-6[/TD]
[TD]Samuel Rodriguez
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date: 04/18/2019: Rm-7[/TD]
[/TR]
[TR]
[TD]Rm-7[/TD]
[TD]Allison Taylor[/TD]
[/TR]
[TR]
[TD]Rm-7[/TD]
[TD]Jonathan Thompson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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.
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]