Transforming columns of data into new rows

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!

Datasets

Regex

Parse

Best Practices

Transformation

Accepted answers

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

New Workflow6.yxmd

All comments

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

Here's the excel sheet

ExampleCensus.xlsx

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

New Workflow6.yxmd

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

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15878

MarqueeCrew 13708

apathetichell 13703