I need an excel expert

I have 450 beds in my building where I house students. Each bed is labeled A101a, A101b, A101c, and then next room is labeled A102a, A102b, A102c, and on. There are 30 rooms per floor with 3 beds per room for a potential of 90 people per floor. There are 5 floors in the building for a total of 450 beds. We work in two shifts, one is called days and one is called swings both with different sleep patterns. What I would like to be able to do is have two sheets in an excel document. The first sheet I would copy and paste 450 names into column A. I would copy and paste the giant list of assigned bed numbers into column B. I would copy and paste the assigned shift the students work into column 3. At this point you would see a name, the bed number, and the shift on the same line on this first sheet. I would then have the second sheet reference that data set and have a neat roster showing me in order 90 names in bed roster order pull the name and shift over on line with the specific bed number. So on the second sheet it would have A101A through A130C in cells in order and the document has a formula that pulls over the name that has the same bed number. It then tells me which beds are open by showing the empty space. Each of the 5 floors would then be printable on 5 - 8.5by11" sheets with 90 spaces on each sheet. Just some additional notes about the document: Column headers under the data tab should be as follows: 1. Unit 2. PLT 3. Rank 4. Name, Full 5. MOS 6. Component 7. Gender 8. SSN 9. DODID 10. DOB 11. Age 12. Barracks 13. Room 14. BASD 15. PEBD 16. ETS Date 17. Clearance Type 18. RFO - Has Clearance 19. Arrival Date 20. Class Start Date 21. Class # 22. Cadre Remarks 23. Shift Schedule 24. Grad Date 25. Class End Date 26. HO - Hold 27. RFO - Errors 28. UCMJ 29. Phase 30. Phone 31. Dental Category 32. Medical Category 33. Profile 34. Profile Start Date 35. Profile Days Left 36. Profile Expiration Date 37. Risk Level 38. Risk Notes 39. Married 40. Emergency Contact Name 41. Emergency Contact Number 42. Allergies 43. Cold Weather 44. Hot Weather 45. DX Days Remaining 46. DX Submitted 47. DX Complete 48. DX Notes Each of these categories should have no restriction on the type of data input on it. The bed roster should be pulling the data from column 13. Columns 4, 23, and 29 should also be visible next to the name on the bed check roster. They will have “OPH” as the room number and we would like all those to be moved to the bottom.