How to transpose long list of groups of rows into columns (with code/formula)

Question

I have data that I want to transpose, which should be easy enough. However, There's multiple rows I need to transpose in groups. Each last row in each group has a certain TEXT in there, so I don't know if there is some way to create a range searching for text versus a range? (Would this work? =if(isnumber(search(“TEXT”,A1)). I tried creating a macro, but obviously I need the code to transpose the data down the spreadsheet and not the same data over and over. Any help would be great!

Here is a visual of what I need (Since I cannot format it, it looks funny, and since I'm new I couldn't embed it, so I included a link of a visual of what the rows/column look like/should look like:

From this:

Column A
Row A1
Row A2
Row A3
Row A4
Row A5
Row A6
Row A7
Row A8
Row A9
Row A10
Row A11
Row A12
Row A13

To this:

Col A   Col B   Col C   Col D   Col E
Row A1  Row A2  Row A3  Row A4
Row A5  Row A6  Row A7  Row A8  Row A9
Row A10 Row A11 Row A12 Row A13

Here's the loop code I found:

Sub Test1()
'UpdatebyExtendoffice20161222
      Dim x As Integer
      Application.ScreenUpdating = False
      ' Set numrows = number of rows of data.
      NumRows = Range("A1", Range("A8”).End(xlDown)).Rows.Count
      ' Select cell a1.
      Range("A1").Select
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
         ' Insert your code here.

         ' Selects cell down 7 row from active cell.
         ActiveCell.Offset(7, 0).Select
      Next
      Application.ScreenUpdating = True
End Sub

Answer 1

Say we start with:

and we want to re-organize into rows with the last item in each row being New. This code:

Sub ReOrg()
    Dim i As Long, j As Long, N As Long, K As Long
    Dim kk As Long
    i = 1
    j = 2
    K = Cells(Rows.Count, "A").End(xlUp).Row

    For kk = 1 To K
        Cells(i, j).Value = Cells(kk, 1).Value
        j = j + 1
        If Cells(kk, 1).Value = "New" Then
            i = i + 1
            j = 2
        End If
    Next kk
End Sub

will produce: