extracting multiple 46 rows of data from a single column of over 2500 rows

Hi,
Ok, the title might sound simple and it is quite easy to do manually, but it takes for ever to do. I am trying to sort the data from a horse racing form list for 13 horses. (and before you ask, the selection programme I've already written might fail totally, It's about the journey) So there is up to 24 lots of horse form data, each lot starting with 1 then 2 then 3 for each horse etc etc up to a possible 24 horses for the Melbourne Cup. So we know the start of each lot of data (1,2,3,4 etc) and we know the data for each horse finishes 46 rows later. The catch 22 is that, between each lot of 46 rows of data I require for each horse, the number of rows in between is a variable. could be 20 rows, could be 30 rows??
So, If I have the column of 2500+ rows of data in column A, I want to extract the 46 rows of data for each horse to start at column B1 for 46 rows, then C1 same, then D1, E1, F1, G1, etc etc etc. Hope this makes sense and hope someone can help. Thanks in advance. attachment for example "Horse form data"

You cannot use 1,2,3 etc as start of data because these numbers appear in many rows which are not the "Start" of data for a given horse. Unless there is data which will always find the start of the data for a given horse, it's mission impossible.

Thanks for the reply. Not exactly what I wanted to hear. Would it be possible if next to the column of data I listed the horses in the race. Could it be done for the 44 rows down from each horses name?? This would give a fixed starting point.

The following code will extract data based on name of horse. It currently takes the names from column C onwards and retrieves the 46 rows you required.

The output is to Sheet3.

Change highlighted code to suit

Sub Find_horses()


    Dim FindString As String
    Dim Rng As Range
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet3")
    
    With ws1
    
    For c = 3 To 6
    
        FindString = .Cells(3, c)  ' Horse's name
        
        If Trim(FindString) <> "" Then
            With Sheets("Sheet1").Range("A:A")
                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    Rng.Offset(-2, 0).Resize(46).Copy ws2.Cells(1, c)
                Else
                    MsgBox "Nothing found"
                End If
            End With
        End If
    
    Next c
    
    End With


End Sub

Thanks. Both worked although the Macro was a little harder to adapt. The formula from bebo seems the best although I had to increase the ranges. Can you explain it to me in plain English. I think I have most f it worked out but couldn't figure why the 2nd part indexed was from A4?? I changed it a few times up and down and it only worked on A4. Why??? Thanks again

=INDEX($A$1:$A$2396,MATCH(C$1,INDEX(($A$4:$A$239<0)*$A$1:$A$236,),0)+ROWS($1:1))

A4 is used to determine where the start of data is:

Take first horse:

Negative number in A22 which is cell 19 starting from 4 : the $A$4:$A$239<0 test will return 1 (TRUE) at A22 (0 for all others).

The A1:A236 multiplies all the results by the contents in A which result in 0/1 if A contains a number, #value if text. The MATCH then matches value in (say) C1 and in this case finds a match at A19.

The ROWS($1:1) adds 1 to start the data following the "1": copying down simply increments ROWS

Make sense??

All good Thanks