Example:
This is capable to handle even if the rows for each SN group differ.
Sub test()
Dim txt As String, x, y, i As Long
With Range("a1").CurrentRegion.Columns(1)
txt = Join(.Parent.Evaluate("transpose(" & .Columns(1).Address & ")"), vbLf)
End With
With CreateObject("VBScript.RegExp")
.MultiLine = True
.Pattern = "^(SN=)"
txt = Mid$(txt, .Execute(txt)(0).firstindex + 1)
.Global = True
txt = .Replace(txt, Chr(2) & "$1")
End With
x = Split(Mid$(txt, 2), Chr(2))
For i = 0 To UBound(x)
y = Split(x(i), vbLf)
Cells(i + 1, "c").Resize(, UBound(y) + 1).Value = y
Next
End Sub
Bookmarks