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

[markpgates]

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!!

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Using offset to convert one column to multiple rows when data is inconsistent - HELP!
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.