transpose every row that starts with the same number

[Marcela]

Thanks Hui for your prompt reply and for asking me to post my question in this forum.

In the attached file, I have a list of elements (all in column A). I want to transpose all blocks of (5) rows that start with the same number to 5 columns and then delete column A. The numbers correspond to Bible verses (so maximum digits will be 3).

I do not know code, but I think that the "reasoning" would be:

If the cell in A(row) starts with a letter, copy it to columns C through G.

If it starts with a number, take the 5 rows that start with the same number and transpose them to columns C through G.

Then go to the next block of 5 rows that start with the next number and transpose, etc.
Until you get to a cell that starts with a letter, in which case, copy it to columns C through G.

I hope this is clear from the file I uploaded.

Thank you in advance.

 

[YasserKhalil]

Hello Marcela
Can you post the expected output so as to be easier for those who need to help you?

 

[Marcela]

Thank you, YasserKhalil, for trying to help. I am uploading again the excel file. The input is in light red, and the output is in green.

 

[YasserKhalil]

Try this code

Sub Test()
    Dim arr        As Variant
    Dim temp        As Variant
    Dim i          As Long
    Dim j          As Long
    Dim p          As Long

    arr = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    ReDim temp(1 To UBound(arr, 1), 1 To 5)
    p = 1

    For i = LBound(arr, 1) To UBound(arr, 1)
        If IsNumeric(Left(arr(i, 1), 1)) Then
            For j = 1 To 5
                temp(p, j) = arr(i + j - 1, 1)
            Next j
            'Change 5 To 1 If Needed No Empty Rows
            p = p + 5
            i = i + 4
        Else
            For j = 1 To 5
                temp(p, j) = arr(i, 1)
            Next j
            p = p + 1
        End If
    Next i

    Range("I1").Resize(1, 5).Value = Array("ESV", "RV1960", "RVC", "NIV", "NVI")
    Range("I2").Resize(UBound(temp, 1), UBound(temp, 2)).Value = temp
End Sub

 

[Marcela]

No luck. Please note that my only input is column A. Thank you, nevertheless.

 

[YasserKhalil]

Try the workbook after applying the code (I just change the target cell for results)

 

[Marcela]

YAH!! Now it worked. Thanks so very much. I'll be trying it on the real file now.

 

[YasserKhalil]

You're welcome. Glad I can offer some help

 

[Monty]

You're welcome. Glad I can offer some help

Good piece of code....Hatsoff

 

[YasserKhalil]

Thanks a lot Mr. Monty for your kind words
I am just a learner and you are a great master in VBA (I am a follower of your replies and I learned a lot from you)