Need help with employee census.

I have an excel spreadsheet that contains an employee census. These employees do not have employee ID numbers, but are identified by their SSN. I also have a list of the employee's dependents in a separate excel spreadsheet. The dependents are tied to the employee by the employee's SSN. How do I merge the two sets of data together so that the family unit is together alphabetically by the employee last name? In other words, I want the employees sorted in alphabetical order by employee last name, with the spouse listed under the employee in the next row, and then dependent children listed under the spouse in the next rows. In my columns, I have Employee SSN, Last Name, First Name, Relationship (Employee, Spouse, Child). I cannot sort alphabetically because some family members have different last names. Any suggestions? Thank you.

Welcome to the forum.

My first suggestion is that you provide us with a small realistic sample.
Help us to help you!

I am new to this site, but hopefully I uploaded it the sample data set correctly.

The first few columns are how my data appears when it is pulled from the HR data base.
The second set of columns is how I need the data to look.
I need to have the employees in alphabetical order with each dependent listed under them.
The SSN is what ties the dependent to the employee.

Any help you can provide is greatly appreciated. I have searched online for days and cannot figure out how to do this.

Thanks so much.