Transforming columns of data into new rows

[ltensmeyer]

I have a census that is formatted strangely:

Currently, we have an employee followed by a dependent, their relationship, and DOB. This pattern just repeats if there are multiple dependents. I'm trying to get this table like this:

With each dependent having their own row of information (including the employee they are a dependent of). Any ideas on the most effective way to achieve this?

Thank you!

[Luke_C]

Can you attach the data in excel rather than a screenshot?

[ltensmeyer]

Here's the excel sheet 

[Luke_C · Accepted solution]

Hi @ltensmeyer 

Here's an example using the transpose/cross tab tools. I also used the multi-rows to help separate multiple dependents.

[ltensmeyer]

@Luke_C  Exactly what I was looking for! Thank you!