Irregular Data Set

[IdabaMalouki]

I have a data set I copy and paste from a website. Someone helped me write a VBA code to sort the data:

Option Explicit

Sub demo()
Dim a, b
Dim i As Long, j As Long, n As Long

a = [A1].CurrentRegion
ReDim b(1 To UBound(a, 1), 1 To 7)

For i = 2 To UBound(a, 1) Step 5
    n = n + 1
    b(n, 1) = a(i, 1): b(n, 2) = a(i + 2, 1): b(n + 1, 2) = a(i + 4, 1)
   
    b(n, 3) = a(i, 2): b(n, 4) = a(i + 1, 2)
    b(n, 5) = a(i, 3): b(n, 6) = a(i + 1, 3): b(n, 7) = a(i, 4)
    n = n + 1
    b(n, 3) = a(i + 3, 2): b(n, 4) = a(i + 4, 2)
    b(n, 5) = a(i + 3, 3): b(n, 6) = a(i + 4, 3): b(n, 7) = a(i + 3, 4)
   
Next i

[N2].Resize(n, 7) = b

End Sub

Can some help write a similar VBA for the new irregular data set in this excel workbook that will organize data in column A into format of adjacent columns:

Thanks in advance

 

[Alex Blakenburg]

I will leave it with Dante since he on top of it but the symbol is commonly referred to as a "Pipe" symbol and on my keyboard is above the "\".
It is often used to as a seperator to show that the text was a concatenation of 2 or more other bits of data.
Since you are online now, can I suggest you provide an image of the two 14 row items so that Dante can see what lines are being dropped off (and is it the same on both).

 

[IdabaMalouki]

Sep 14 | 7:45pm
Kent State Golden Flashes
Tennessee Volunteers
Spread
49
-110
-49
-110
Money
Total
62.5
-110
62.5
-110
Sep 14 | 8:00pm
Rice Owls
Houston Cougars
Spread
4
-115
-4
-105
Money
158
-190
Total
44.5
-110
44.5
-110

 

[DanteAmor]

Yes, each block begins with that strange date format with a vertical separator

These texts will always appear:

Spread
Money
Total

 

[IdabaMalouki]

Sep 14 | 3:30pm
Ball State Cardinals
Miami (FL) Hurricanes
Spread
36.5
-110
-36.5
-110
Money
Total
54
-110
54
-110
Sep 14 | 3:30pm
Tulane Green Wave
Oklahoma Sooners
Spread
13.5
-110
-13.5
-110
Money
430
-600
Total
47.5
-110
47.5
-110

 

[IdabaMalouki]

Thank you, kind sir! One more thing. I would like to separate date and time. Is there a formula I can write into adjacent cell to separate "Sep 14 | 3:30pm" to extract just the 3:30pm into adjacent cell?

 

[DanteAmor]

These texts will always appear:

Spread
Money
Total

Could you explain a little more about your data. Which values will always be there and which may not be there.

 

[DanteAmor]

You can mark with a color the rows that will always have some data. And mark with another color those that may or may not have a value.

 

[IdabaMalouki]

I copy and paste data into excel from this website: https://sportsbook.caesars.com/us/dc/bet/americanfootball?id=b7eda1b3-0170-4510-9616-1bce561d7aa7

Will notice down the page that one portion under header Money is blank, since there are no numbers under that block. So when posted into the excel that specific block drops to 14 lines

Is there a VBA code that could just scrape data into Excel?

 

[DanteAmor]

I can't download the file.
It would be ideal if you explained here what the patterns of your data would be.
Use images to support your explanation.

I'm trying to help you make the code. But you must help me with the explanations I requested.

Or the other option is to manually insert the missing rows into your blocks and then run the macro that I provided in post #4.

 

[IdabaMalouki]

I don't mind the manual option. Thanks for everything.