Ecg

[Iamsuyog]

Patient	T1	T2	T3	V4	T5	T6	T7	T8	V1
1	5
1		6
1			7
2		10	11	15	16	17
3		14
3				15
4	11
5					18
5									19

<tbody>
</tbody>

i want all result in perpenducular

Patient	test	result
1	T1	5
1	T2	6
1	T3	7
2	T2	10
2	T3	11
2	V4	15
2	T5	16
2	T6	17
3	T2	14
3	V4	15
4	T1	11
5	T5	18
5	V1	19

<tbody>
</tbody>

so on

kindly help me to solve this issue?

 

[wideboydixon]

Public Sub ECG()

Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim thisRow As Long
Dim thisCol As Long
Dim nextRow As Long

' Set the source sheet and find the last row and column
Set sourceSheet = ActiveSheet
With sourceSheet
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

' Create a new target sheet and add the headers
Set targetSheet = Worksheets.Add
With targetSheet
    .Cells(1, 1).Value = "Patient"
    .Cells(1, 2).Value = "Test"
    .Cells(1, 3).Value = "Result"
End With
nextRow = 2

' Process the grid
For thisRow = 2 To lastRow
    For thisCol = 2 To lastCol
        If sourceSheet.Cells(thisRow, thisCol).Value <> "" Then
            targetSheet.Cells(nextRow, 1).Value = sourceSheet.Cells(thisRow, 1).Value
            targetSheet.Cells(nextRow, 2).Value = sourceSheet.Cells(1, thisCol).Value
            targetSheet.Cells(nextRow, 3).Value = sourceSheet.Cells(thisRow, thisCol).Value
            nextRow = nextRow + 1
        End If
    Next thisCol
Next thisRow

End Sub

WBD