Advanced columns to rows with example attached

I have a file (example attached) that has a column with names, followed by a column containing that person's contact information, followed by a column with that person's rank. The names are listed as "Last, First MI" and have from 0 to 4 blank cells beneath them depending on how much contact information is included for each individual. The contact info never has an empty cell, but they are listed in the same order as long as that information is provided (Address, "City, ST ZIP", Email, Home Phone, Cell Phone), and the rank is in cells the same way as the names. Each new name is in the cell on the column to the left of it's first address information and the column to the right shows their rank. Any additional address information has blank cells to its right and left.

There are about thousands rows of this, with about 1500 names. I need this information to be listed in the following manner (if possible):

First Name/Last Name/MI/Address/City/State/ZIP/Email/Home Phone/Cell Phone/Rank

I have been able to do this by copying everything into Word, where it is all listed separated by ^p. I then change each ^p into a ^t as long as it's not followed by a ^#.^#^# (which is how the ranks are formatted). But with this, the address information and ranks go into random columns depending on how much address info was included for that individual.

My example includes how I have the information now and how I would like it to be listed. I appreciate any feedback. I am not very good with macros, but will figure it out if that is the best way to solve this issue.

Thank you in advance for taking the time to read through this.