VBA Excel offset formula required for the below excel sheet

Question

Please find the image of below excel sheet. I need VBA code for this one.

and result should be

.

A column combines with after b,c,d,e,f,g. Only bring the "YES" word and "NO" no need.

Answer 1

Here is the VBA code, if you want to acheive it through the VBA only.

Here is source Sample image

And here is the Destination Image from the same sheet.

Sub TransposeAndFill()
'Declare variables
Dim ws As Worksheet
Dim i As Long
Dim j As Long
Dim r As Long
Dim src As Range
Dim dst As Range

'Set worksheet and ranges
Set ws = ActiveSheet
Set src = ws.Range("A1:G12") 'Source range
Set dst = ws.Range("I1:K1") 'Destination range

'Copy headers
ws.Range("I1").Value = ws.Range("A1").Value
ws.Range("J1").Value = "Seq"
ws.Range("K1").Value = "Result"

'Initialize row counter for destination range
k = 2

'Loop through columns of source range (except first column)
For i = 2 To src.Columns.Count
    'Loop through rows of source range (except first row)
    For j = 2 To src.Rows.Count
        'Copy site value from first column
        dst.Cells(k, 1).Value = src.Cells(j, 1).Value
        'Copy seq value from column header
        dst.Cells(k, 2).Value = src.Cells(1, i).Value
        'Copy result value from cell
        dst.Cells(k, 3).Value = src.Cells(j, i).Value
        'Increment row counter for destination range
        k = k + 1
    Next j
    'k = k + 1
Next i 

End Sub