Transpose sets of multiple columns into rows?

[pythorlh]

I have data that is poorly normalized.

Something like this:

student ID	Class	Score	Grade	Class2	Score2	Grade2	Class3	Score3	Grade3
1	History	95	A	English	65	D-	Science	85	B
2	Science	90	A-	Math	80	C-

And in the end I want this:

Student ID	Class	Score	Grade
1	History	95	A
1	English	65	D-
1	Science	85	B
2	Science	90	A-
2	Math	80	C-

I'm trying Transpose, then trimming the numbers off the name column, but I don't know how to bring the 3 records back together correctly.  I'd rather not do it manually, because there are actually 121 of each field (Class, Score, Grade).

[JosephSerpis · Accepted solution]

Hi @pythorlh I have mocked up a workflow that produces you output. It uses a regex tool to replace the numbers in your column names. Then I use a Multirow tool to create a rank per student ID for each class they attend, which when you use a crosstab tool produces a row per class for each student ID.

[Garrett]

I think you can probably get this done with a single "Arrange" tool (perhaps followed by a "Filter" tool to get rid of any nulls):

[pythorlh]

Thank you, I was able to get this to work.

One note for anyone reading this, make sure if you have any Select tools in your workflow before the Multi-Row Formula Tool that they don't alter the sort of the incoming fields.  I was having trouble until I realized this was happening.