Transpose A Column Of Data In To A Table

[gunjan.nasit]

i have to transpose the column data in to row.

in this data the first cell data is of font size 13.5 after that some cells are of font size 10 after that further one cell of font size 13.5 and another are of font 10.....

I have to do transpose this column data in such away that
first cell of 13.5 size data will copy in sheet 2 A1, further data of size 10 will copied to B1, C1, D1....till the second 13.5 sized cell came.
when the 13.5 sized cell came in recognition it will copied to A2, further data of size 10 will copied to B2, C2, D2....till the second 13.5 sized cell came, and continued to the end of the data.....

i have attached format for desired data transpose as i have described in red font in excel
Please, help me out for this problem by Macro/VBA code.....

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[Excel Fox]

This will collate the data in a new sheet

Sub Consolidator()

    Dim rngInput As Range
    Dim lng As Long, lngCol As Long
    Set rngInput = Worksheets("Sheet1").Range("A2:A15000")
    Worksheets.Add After:=Sheets(Sheets.Count)
    For Each rngInput In rngInput
        If IsEmpty(rngInput) Then Exit For
        If rngInput.Font.Bold Then
            lng = lng + 1
            lngCol = 1
        Else
            lngCol = lngCol + 1
        End If
        Sheets(Sheets.Count).Cells(lng, lngCol).Value = rngInput.Value
    Next rngInput
    
End Sub

[gunjan.nasit]

thank you for solving....

Have you tried this code in the attached file..????

i had a runtime error "1004"
application defined or object defined


when i had debug program it has shown yellow background behind the following line of code

Sheets(Sheets.Count).Cells(lng, lngCol).Value = rngInput.Value

i think in this line there is some problem...

[Excel Fox]

Working fine for the sample you've posted. Are you using it on the same file or different?

[gunjan.nasit]

ok now it works but it can removes all formatting and hyperlinks....

please try to keep the formatting after the running code.....