Using offset to convert one column to multiple rows when data is inconsistent - HELP!

Afternoon all,

I have a data set that unfortunately is one long column that I am trying to convert into usable data into multiple rows.
I have been using the formula;

=OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*9,0)

However the data is inconsistent - Sometimes there are 9 elements before the next set, sometimes 8.
An example;

Sometimes the pattern will be; Name/Date/Time etc
the next pattern will be; Name/Time etc

So hence the formula above gets confused (almost as much as the user!)

Is there any way to use a dynamic formula? - The data is very simple and consistent! Attached if there are any legends out there who can help!!

Thanks all!!

You need to be able to determine where each variable-range record begins. I haven't looked through every one of the 26,000+ cells, but it does seem as if you only have only three names for each record, i.e. Matthew H, MD and Gatesy, and so you could identify those by first inserting a blank row at the top of the file and then using this formula in B2:

=IF(OR(A2="Matthew H",A2="MD",A2="Gatesy"),MAX(B$1:B1)+1,"")

When you copy that down it will give you a simple sequence identifying the start of each record, although I would suggest this amendment:

=IF(OR(A2="Matthew H",A2="MD",A2="Gatesy"),(INT(MAX(B$1:B1)/100)+1)*100,"")

This will give a sequence of 100, 200, 300 etc., which will give you an opportunity to replace the "" with other expressions to try to identify the type of field. For example, it looks like the name is followed by a date (this can be 01 added on to the record number), then a time (02), then the words Bet Stake or Won (03), then an address (04), and so on.

It would then be quite straightforward to extract the appropriate fields in another part of the sheet using an INDEX/MATCH formula in conjunction with ROWS (*100) and COLUMNS.

Hope this helps.

Pete