Create a Database using macro (Copy and paste values)

Hi,

I need to organize a table as a database and I would like to create a macro in order to do this. In the images you can see an example of what I trying to do.

There is a list of hotels with its departments, I need to copy the name of the hotel and paste it in the left column in order to organize each department with its corresponded hotel (replacing the number that is in the column A). The thing is that each Hotel has a different number of departments and I do not know how to paste this names until its last department from the list (it is to say, until the next hotel, or until the last empty cell in the column A).

In the images you can see a better example. There are 68 Hotels.

At the end I would like to remove those rows where the name of the hotel is in both column A and B, in order to use column A for Hotels Name and B just for departments.

Thank you very much!