Move Vertical to Horizontal

[CindyCas]

I have a list of students and grades for multiple courses. I need to change this so that all of the classes/grades for each student are listed in the same row instead of multiple rows.

This is what I have.

Student ID	student name	course	Code	current score	final score	unposted current score	unposted final score
2212174740​	Student A	Foundations Clinical	Clinical		0​		0​
2212174740​	Student A	Foundations of Practical Nursing	PN104	79.04​	13.09​	79.04​	13.09​
2212174740​	Student A	IV Therapy	IV202	86.67​	13​	86.67​	13​
2109162949​	Student B	Career Readiness	CD101	73.28​	7.46​	73.28​	7.46​
2109162949​	Student B	Nursing III	PN301	88​	66.03​	88​	66.03​
2109162949​	Student B	Clinical Term 4 -Akhere	CLINICAL	100​	63.16​	100​	63.16​

So instead of having the student listed multiple times/multiple rows the student would just be listed once with all the courses in the same row.

This is what I need:

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Lists
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

How about

Excel List Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Student ID	student name	course	Code	current score	final score	unposted current score	unposted final score
2	2212174740	Student A	Foundations Clinical	Clinical		0		0
3	2212174740	Student A	Foundations of Practical Nursing	PN104	79.04	13.09	79.04	13.09
4	2212174740	Student A	IV Therapy	IV202	86.67	13	86.67	13
5	2109162949	Student B	Career Readiness	CD101	73.28	7.46	73.28	7.46
6	2109162949	Student B	Nursing III	PN301	88	66.03	88	66.03
7	2109162999	Student C	Clinical Term 4 -Akhere	CLINICAL	100	63.16	100	63.16
8
9
10
11
12
13
14	2212174740	Student A	Foundations Clinical	Clinical	0	0	0	0	Foundations of Practical Nursing	PN104	79.04	13.09	79.04	13.09	IV Therapy	IV202	86.67	13	86.67	13
15	2109162949	Student B	Career Readiness	CD101	73.28	7.46	73.28	7.46	Nursing III	PN301	88	66.03	88	66.03
16	2109162999	Student C	Clinical Term 4 -Akhere	CLINICAL	100	63.16	100	63.16
Sheet1

Cell Formulas
Range	Formula
A14:T16	A14	=LET(u,UNIQUE(A2:B7),m,MAX(COUNTIFS(A2:A7,TAKE(u,,1)))*6,HSTACK(u,DROP(REDUCE("",TAKE(u,,1),LAMBDA(x,y,VSTACK(x,EXPAND(TOROW(FILTER(C2:H7,A2:A7=y)),,m,"")))),1)))
Dynamic array formulas.

 

[DanteAmor]

Hi @CindyCas
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

If it's few records, the following version has quite short code:

Sub student_v1()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range, f As Range
  Dim lc As Long
  
  Set sh1 = Sheets("Sheet1")    'Fit to your Source sheet name
  Set sh2 = Sheets("Sheet2")    'Fit to your Target sheet name
  
  sh2.Cells.ClearContents
  sh2.Range("A1").Resize(1, 8).Value = sh1.Range("A1").Resize(1, 8).Value
  For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(3))
    Set f = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
    If f Is Nothing Then
      sh2.Range("A" & Rows.Count).End(3)(2).Resize(1, 8).Value = c.Resize(1, 8).Value
    Else
      lc = sh2.Cells(f.Row, Columns.Count).End(1).Column + 1
      sh2.Cells(1, lc).Resize(1, 6).Value = sh1.Range("C1").Resize(1, 6).Value
      sh2.Cells(f.Row, lc).Resize(1, 6).Value = c.Offset(, 2).Resize(1, 6).Value
    End If
  Next
End Sub

But if there are hundreds of records, this version is faster:

Sub student_v2()
  Dim sh1 As Worksheet
  Dim dic As Object
  Dim a As Variant, b As Variant, c As Variant, ky As Variant
  Dim i&, j&, k&, nMax&, y&, nRow&, nCol&
  
  Set sh1 = Sheets("Sheet1")
  a = sh1.Range("A1:H" & sh1.Range("A" & Rows.Count).End(3).Row).Value
  Set dic = CreateObject("Scripting.Dictionary")
  
  For i = 2 To UBound(a, 1)
    dic(a(i, 1)) = dic(a(i, 1)) + 1
    If dic(a(i, 1)) > nMax Then nMax = dic(a(i, 1))
  Next
  ReDim b(1 To dic.Count, 1 To (6 * nMax) + 2)
  
  dic.RemoveAll
  For i = 2 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      y = y + 1
      dic(a(i, 1)) = y & "|" & 3
      b(y, 1) = a(i, 1)
      b(y, 2) = a(i, 2)
    End If
    nRow = Split(dic(a(i, 1)), "|")(0)
    nCol = Split(dic(a(i, 1)), "|")(1)
    
    k = 3
    For j = nCol To nCol + 5
      b(nRow, j) = a(i, k)
      k = k + 1
    Next
    
    nCol = nCol + 6
    dic(a(i, 1)) = nRow & "|" & nCol
  Next

  With Sheets("Sheet2")
    .Range("A1").Resize(1, 8).Value = sh1.Range("A1").Resize(1, 8).Value
    k = 9
    For j = 2 To nMax
      .Cells(1, k).Resize(1, 6).Value = sh1.Range("c1").Resize(1, 6).Value
      k = k + 6
    Next
    .Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End With
End Sub

You decide which macro to use.
------------------------------------

HOW TO INSTALL MACROs
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (student_v1 o student_v2) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------