Need a New Row at Every Instance of a Certain Character (or every X columns)

You mentioned in your first post that the data has already been transposed from a single column format. It might be easier to work with that directly, so could you post an example in that format with some representative data?

Pete

Here is a sample of the data vertically, as it pastes into Excel, and also with a shorter Incoming record. Sample_NewRows2_8-16-13.xlsx

Thank you for all of your help so far.

Hi,

I've been out all day, so apologies for the delay in getting back to you.

The attached file shows how you can achieve your required output using two formulae. First of all, though, you must insert 5 new blank rows above your data in Sheet4 and put "D" in A5. Then you can put this formula in B6:

Formula:

=ROUND(IF(A5="D",INT(MAX(B$5:B5))+1,B5+IF(AND(A1="I",A7="D"),0.2,0.1)),1)

and copy it down to the bottom of your data (double-click the Fill handle).

Then insert a new sheet, use row 1 for headers, and put this formula in A2:

Formula:

=IFERROR(INDEX(Sheet4!$A:$A,MATCH(ROWS($1:1)+(COLUMNS($A:A)-1)/10,Sheet4!$B:$B,0)),"")

and copy it across to I2. Then apply the appropriate formatting to those cells, and then copy the whole row down as far as you need it.

Hope this helps.

Pete

Hi Pete,

Thank you for all of your help.
unfortunately, my data is still coming out "offset". (See here: Excel4.jpg)
I can tell because of the headers (DATE, O/I, EXT, TRK, NUMBER, etc), as DATE should have fallen in column A and NO should have fallen in column I.

But Pete, you've done so much work and helped an immense amount. I think I can fiddle with the data from here to get what I need, as asking you to do something that might be impossible is probably a little unreasonable to ask of already very-helpful free help. This is MUCH easier to work with than having to move each record around manually.
Thank you!

Well, seeing your snapshot it looks as if your data is not consistent. Your 4th record is not an "I" record, but the 7th field is missing. It starts to go badly wrong at record 9, where you have an asterisk (maybe this means to miss or insert a few fields), and the "I" character is in totally the wrong place. The asterisks appear later on as well.

You need to have a clear definition of how the data is laid out - there must be some logic which determines how the data comes to you, so it is just a matter how getting to the bottom of that logic.

Pete