Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data

[coronak]

I am a completely ignorant in how to make macros. A colleague used to use one to help us send out letters for students with poor grades. If anyone can help craft a macro let me know. This is a sample of the data I'm working with (names and addresses changed for obvious reasons).

Last Name	First Name	Grade	Student ID	Course title	Mark2	Residence Address	Residence City	Res State	Res Zip
Johnson	Kevin	12	916721	Health	D+	222 W. Creek	Amityville	TX	92888
Johnson	Kevin	12	916721	Intensive Rdng	D-	222 W. Creek	Amityville	TX	92888
Lopez	Sally	11	876675	Geometry	D	111 Chestnut	Bellfront	CA	92225
Derrick	Kelly	11	886512	US History	F	1234 Primrock	Rosecrest	CA	85545

What I'm wanting to do is modify the above data so students with multiple grades whose names appear as duplicates in columns have their classes and grades transposed onto the same row, so for example, Kevin Johnson on the above list would have his classes and grades listed out as follows instead of having his name duplicated. I believe there are students that could have up to 7 grades on this document. The document has about 1338 rows right now and I'm hoping to condense it:

Last Name	First Name	Grade	Student ID	Course title	Mark2	Course Title	Mark 2	Residence Address	Residence City	Res State	Res Zip
Johnson	Kevin	12	916721	Health	D+	Intensive Reading	D-	222 W. Creek	Amityville	TX	92888

If this is not an easy ask I understand. When my coworker left us she took her macro and now we don't know how to run these numbers so we can do a mail merge and mail out letters to notify parents of D's and F's. If you're are able to help with a macro it'd be appreciated.

Thank you!

 

[Kaper]

Some years ago I'd say let's re-create the macro. But today, my proposition is to do something similar by clicking in Power Query.

I did it (one of operations is not clicked-in but some manual editing was necessary)already for you so I'll share it:
1) create new workbook,
2) from ribbon: Data->Get & Transform->Get Data->From other sources->Empty Query
3) PQ editor will open. Click Home(shall be active already)->Advanced Editor on PQ editor ribbon
4) replace code of empty query with the following:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Kaper\test\bad_grades.xlsx"), null, true),
    My_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(My_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Last Name", type text}, {"First Name", type text}, {"Grade", Int64.Type}, {"Student ID", Int64.Type}, {"Course title", type text}, {"Mark2", type text}, {"Residence Address", type text}, {"Residence City", type text}, {"Res State", type text}, {"Res Zip", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Course_and_Mark", each [Course title] & ": " & [Mark2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Course title", "Mark2"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Student ID"}, {{"All_Courses", each Text.Combine([Course_and_Mark], ", "), type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Student ID"}, #"Removed Columns", {"Student ID"}, "Output", JoinKind.Inner),
    #"Expanded Output" = Table.ExpandTableColumn(#"Merged Queries", "Output", {"Last Name", "First Name", "Grade", "Residence Address", "Residence City", "Res State", "Res Zip"}, {"Last Name", "First Name", "Grade", "Residence Address", "Residence City", "Res State", "Res Zip"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Output", {"Student ID"})
in
    #"Removed Duplicates"

Remember to correct the path and name of your file with grades (in Source line). Also if the sheet with marks is not Sheet1 then change Sheet name (Item="Sheet1") in next line.
5) Close advanced editor (button Done) and Click on Close and load.

Now you have your source file for mailmerge ready. You will notice that I did just one column with course(s) and Mark(s) as this way it will be better suited for mail merge (just one field not many fields (some empty for second or further negative mark).

To make life even easier you may experiment with adding new line between 2 or more problematic courses - use such modification (and format cells in excel column B as wrapping) :

    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Student ID"}, {{"All_Courses", each Text.Combine([Course_and_Mark], ", #(lf)"), type text}}),

PS if you'd have excel 365 (but I think it is not available in 2021) you could also use function GROUPBY to have such result in even easier way