Fill down the cell based on the cell value

[mayaa_mmm]

I used to download the report from our site, it always gives the report in the below format.

A	B	C
Class
Subject	Maths
Mark details
Semester I	Semester II	Semester III
10	30	50
50	25	100
100	20	40
Class
Subject	Science
Mark details
Semester I	Semester II	Semester III
10	30	50
100	20	40
50	25	100

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I want the report in the below format.
Every time i need to remove the rows and manipulate the output to get the below output format.

Subject	Semester I	Semester II	Semester III
Maths	10	30	50
Maths	50	25	100
Maths	100	20	40
Science	10	30	50
Science	100	20	40
Science	50	25	100

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Kindly help with this issue

 

[Peter_SSs]

Try this with a copy of your workbook.
It may need some tweaking as website data is often not as 'clean' as other data.

Sub Rearrange()
  Dim Data, Results
  Dim ubResults As Long, i As Long, k As Long
  Dim Subj As String
  
  Data = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Resize(, 3).Value
  ubResults = Application.Count(Columns("A"))
  ReDim Results(1 To ubResults, 1 To 4)
  k = 1
  Do
    i = i + 1
    If Data(i, 1) = "Subject" Then
      Subj = Data(i, 2)
      i = i + 3
      Do While IsNumeric(Data(i, 1)) And i < UBound(Data, 1)
        Results(k, 1) = Subj
        Results(k, 2) = Data(i, 1)
        Results(k, 3) = Data(i, 2)
        Results(k, 4) = Data(i, 3)
        k = k + 1
        i = i + 1
      Loop
    End If
  Loop Until i >= UBound(Data, 1)
  Range("G2").Resize(ubResults, 4).Value = Results
  Range("G1:J1") = Array("Subject", "Semester I", "Semester II", "Semester III")
End Sub