Consolidated data in the Summary page

[winninghkjc]

HI All,

Nice to meet you all. I am a study currently working as an assistant in an educational institution on a voluntary basis. I am going to consolidated the exam result of students and performing analysis.

The system we currently using is able to generate the result of each student as follow:

​

Name	Mary
Class	1A
	Computer
Test	Assignment	Exam
20	30	40
	Music
Test	Assignment	Exam
40	50	60
Name	Peter
Class	2A
	History
Test	Assignment	Exam
50	30	20
	Music
Test	Assignment	Exam
30	20	40
	Language
Test	Assignment	Exam
30	50	70

<tbody>
</tbody>

I have no idea that if there is any formula I can use / vba function can let me gather the data and copy them to the summary page?

Computer

Music

History

Language

Name

Class

Test

Assignment

Exam

Test

Assignment

Exam

Test

Assignment

Exam

Test

Assignment

Exam

<tbody>
</tbody>

Thank you for yours help and advice.

Many thanks,

jc.

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming ..
- Original data in 'Sheet1' columns A:C.
- Data layout is uniform per your sample. That includes, 3 rows per subject, no blank rows within a student's 'block', a blank row between student 'blocks'.
- Maximum of 10 subjects exist. Edit 'Const' line near start of code if you want. Doesn't matter if this value is greater than the actual number, but must not be less.
- Results to go on to Sheet2.
- Sheet2 already exists and any data already on it can be removed.

.. then try this on a copy of your workbook.

Sub Rearrange()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, fc As Long, dCount As Long
  
  Const MaxSubjects As Long = 10  '<- Change if required
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  With Sheets("Sheet1")
    a = .Range("A1", .Range("C" & .Rows.Count).End(xlUp).Offset(1)).Value
  End With
  ReDim b(1 To UBound(a) / 5, 1 To MaxSubjects * 3 + 2)
  b(2, 1) = "Name": b(2, 2) = "Class"
  k = 2
  For i = 1 To UBound(a)
    If a(i, 1) = "Name" Then
      k = k + 1
      b(k, 1) = a(i, 2): b(k, 2) = a(i + 1, 2)
      i = i + 2
      Do Until a(i, 2) = ""
        If d.exists(a(i, 2)) Then
          fc = d(a(i, 2)) - 1
        Else
          dCount = dCount + 1
          fc = dCount * 3
          d(a(i, 2)) = fc + 1
          b(1, fc + 1) = a(i, 2)
          b(2, fc) = "Test": b(2, fc + 1) = "Assignment": b(2, fc + 2) = "Exam"
        End If
        b(k, fc) = a(i + 2, 1): b(k, fc + 1) = a(i + 2, 2): b(k, fc + 2) = a(i + 2, 3)
        i = i + 3
      Loop
    End If
  Next i
  With Sheets("Sheet2")
    .UsedRange.ClearContents
    With .Range("A1").Resize(k, d.Count * 3 + 2)
      .Value = b
      .Columns.AutoFit
      .Offset(, 2).Resize(.Columns.Count - 2).HorizontalAlignment = xlCenter
    End With
  End With
End Sub

My data:

Excel Workbook
	A	B	C
1	Name	Mary
2	Class	1A
3		Computer
4	Test	Assignment	Exam
5	20	30	40
6		Music
7	Test	Assignment	Exam
8	40	50	60
9
10	Name	Peter
11	Class	2A
12		History
13	Test	Assignment	Exam
14	50	30	20
15		Music
16	Test	Assignment	Exam
17	30	20	40
18		Language
19	Test	Assignment	Exam
20	30	50	70
21
Sheet1

Results:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Computer			Music			History			Language
2	Name	Class	Test	Assignment	Exam	Test	Assignment	Exam	Test	Assignment	Exam	Test	Assignment	Exam
3	Mary	1A	20	30	40	40	50	60
4	Peter	2A				30	20	40	50	30	20	30	50	70
Sheet2

 

[oldbrewer]

DO YOU HAVE ACCESS TO THE RAW DATA IE CLASS, SUBJECT, NAMES AND SCORES

I feel somebody has already processed raw data to produce the table you are showing us...