Change multiple columns into multiple rows

Hi there

I have attached a spreadsheet with a small example of what I want to do...... I have a large spreadsheet that contains and employee number and then up to 5 entries of Dependants (each has its own column). What I need to do is have 1 for each of the dependant information instead of 5 and have multiple rows for the employee if they have more than one dependant.

At the moment I am sorting the data by dep 1 name, pasting the data into a new sheet, deleting all the dep 1 stuff, sorting by dep name 2 and pasting that.........etc.

It doesn’t take too long to do but I just wondered if there might be a quicker way as this will be something I will need to do more often.

Any ideas or thoughts would be gratefully received.

Cheers

Jim

Sheet 1 shows the data at the start and sheet 2 show how it ends up.