your sample data is in this url
there are three macros in the vb editor . the sheet2 is after running the macro "test" all the three macros are to be parked in a module. But you should operate only "test" - the second macro is embedded in this.
the macro "undo" is to undo the results of the macro
you see sheet 2 in the uploaded file AFTER running the macro "test"
if you want to recheck
1.first run "undo"
2. second run macro "test"("test" only)
for completion sake I am giving the macros here itself also
Set cfind = Cells.Find(what:="SL#", after:=Cells(Rows.Count, "A"))
add = cfind.Address
j = cfind.Row
Set cfind = Cells.FindNext(cfind)
If cfind.Address = add Then
k = Cells(Rows.Count, "A").End(xlUp).Row
k = cfind.Row
Range(Cells(j, "A"), Cells(k - 1, "B")).Copy
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial , Transpose:=True
If cfind.Address = add Then Exit Do
j = k
Set cfind = Cells.Find(what:="SL#", after:=Range("A1"))
If cfind Is Nothing Then Exit Do
If cfind.Address = "$A$1" Then Exit Do
Application.CutCopyMode = False