Easiest way to sort my data

Hello!

New to this form. Current School Guidance Counselor looking to format data to best send letters home to high school families for failure warnings.

I can pull a report from PowerSchool for grades <60. The data usually spits out:

Last Name, First Name / Course

There are other columns that are easy to delete and I don't need them with the data pull. And this will repeat for each course that is below a 60. For example:

Smith, John Allen/ ELA
Smith, John Allen/ Math
Doe, Jane Ann/ ELA
Simons, Rick Mike/ History

Each student has 1 or more courses in this report.

Now I am using mail merger to pull this data and input first name, last name, and the failures into these letters to mail home with report cards for full-year failure warnings. My problem is in my same sheet (in which I omitted names) and how the data looks. Sheet 2 is me pulling the First and Last names using Cmd + E (isn't 100% accurate to get names with last names being two words, etc. causing problems, so if there is a better way please let me know. Names are Last, First Middle so makes it kinda annoying).

What I am looking to figure out:
- How to remove the duplicate names of the students. Sometimes the name will list 3 times, and sometimes the name will list once. It's inconsistent depending on how many failures.
- How to format my data to look like my example on sheet 3. I want to have one singular row of first name, last name, course (1,2,3) so when I do my mail merger I can use the headers to input what I am looking for.
- Removing the blanks where repeats have been removed isn't necessary, but if the above is doable removing the blanks would be nice.

Any help is appreciated! Ty!

Administrative Note:

Welcome to the forum.

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.