Change multiple rows to multiple columns with one row

I am trying to save myself a huge manual formatting issue here...

I have data for employees with benefits data although the reports run out of our HRIS gives us seperate rows for EACH benefit. The carrier I need to send it to requires that we have one row per employee with multiple columns for the benefits.

I think its an easy fix but can't figure it out.

Data looks like this:


Clock number, name, address, SSN, benefit, coverage amount

so it looks like this in its current format:

0001 John Doe 123 Main st. 111-11-1111 Life Insurance 50,000
0001 John Doe 123 Main st. 111-11-1111 Long term disability 4,000
0001 John Doe 123 Main st. 111-11-1111 ADD 50,000

There are only rows for benefits that are currently there. Meaning, if someone had ADD insurance there would be row listing, however if another employee did not elect it, there would not be a row listing that.

Here is an attachment with some example data to show what i am trying to do.
data example.xlsx

Thanks!

I understand that you are working with perhaps confidential information, but i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

sounds like a great solution

If you have solved you're (own) question, please mark this thread as solved

I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

No problem, thanks