Copying Data from Sheet1 to Sheet2 with condition

Hi everyone,

Good evening. My first post here and happy to be a part, to learn and to contribute. i am posting a thread today to kindly get some help. I am new to VBA excel and i am working on a task. Please see the attached excel sheet; what I would like to get is,

1. Copy and paste data from Sheet1 into a sheet and that sheet data should be categorised according to the tests.

The final output should look like how it is in Sheet2. (Please note that there is no "Test C" on Sheet 2)

Any help would be very much appreciated.

Very much looking forward to replies.

Thanks guys!!

Try

Sub test()
    Dim a, i As Long, ii As Long, w, txt As String, e, n As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    With Sheets("sheet1").Cells(1).CurrentRegion
        For ii = 1 To .Columns.Count
            If Application.CountA(.Columns(ii).Offset(1)) Then txt = txt & "," & ii
        Next
        a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & _
                            ")"), Split(Mid$(txt, 2), ",")): txt = ""
    End With
    For i = 2 To UBound(a, 1)
        For ii = 4 To UBound(a, 2)
            txt = txt & Chr(2) & a(i, ii)
        Next
        If Not dic.exists(txt) Then
            ReDim w(1 To UBound(a, 2), 1 To 1)
        Else
            w = dic(txt)
            ReDim Preserve w(1 To UBound(a, 2), 1 To UBound(w, 2) + 1)
        End If
        For ii = 1 To UBound(a, 2)
            w(ii, UBound(w, 2)) = a(i, ii)
        Next
        dic(txt) = w: txt = ""
    Next
    With Sheets("sheet2").Cells(1).Resize(, UBound(a, 2))
        .CurrentRegion.EntireColumn.ClearContents
        .Rows(1).Value = a: n = 1
        For Each e In dic
            n = n + 1
            .Rows(n).Resize(UBound(dic(e), 2)).Value = Application.Transpose(dic(e))
            n = n + UBound(dic(e), 2)
        Next
    End With
End Sub

1)

Don't quote whole posts -- it's just clutter.
If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

2) Do a step debug while Local Window open.
While you are in VBE, go to [View] - [Local Window].
Click somewhere n the code, and hit F8
As you hit F8, the code executes one line and you will see all the variables in Local Window.

No problem Jindon, thanks again for your help and support! Cheers.