Excel Active list automatically populating from waiting list after deletion of an active user

Hi I have around 200 lockable locker assigned to various members, and there is a waiting list. Once someone no longer needing a locker, next person from waiting list (oldest date) gets assigned that locker space and that entry from waiting list gets deleted Example GF002 and GF005 in attached file ( see sheets 3 & 4 as they show end result) Can you help me with Automating this process ?