Conditional, moving cells

Okay, I'm not good at excel but my boss wants me to figure out a problem for him.

So, I have a spreadsheet. Its about 48000 records

Most of the records are duplicates, but only because they have multiple dependents and multiple jobs.

So basically its like this. I'm using commas to seperate cells.

SSN, Name, Employer, Dependent SSN
1245, Jane, Walmart, 4321
1245, Jane, Walmart, 1234
1245, Jane, Walmart, 3214
1246, John, Costco, 1122
1246, John, HomeDepot, 1122

Like that so same person but employer or Dependent SSN changes
We want to take Employers/Dependent and add it to the previous line.
so it would look like
SSN, Name, Emp1, Emp2, Emp3, Dep1, Dep2, Dep3
1245, Jane, Walmart, 4321, 1234, 3214
1246, John, Costco, HomeDepot, 1122

To make things more complicated we have information like employer address that needs to be moved with the Employer name, and Dependent Relationship that needs to be pulled with the Dep SSN.

Thanks in advance-- Josh