Split cell text with multile rows of data into separate cells

Hello, something I created over a year ago and I cannot figure how I did this prior, looking for assistance, thanks. I have a report that contains multiple rows of data (names of people) in one cell at time, that I need to separate, in order to create a list of unique IDs. Names could be listed twice in various cells, so duplicates need to be removed.

In addition, and as a bonus, am trying to match each person's rating, which is listed at the top of each column, next to each person. I attempted using Text to Columns, however the names will each contain spaces and commas, so it was splitting too much.

Any suggestions? Attached is a sample report showing the original table, along with what I am looking to accomplish. Thanks for any help/insight.

Sheryl

Thank you !