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

Yes, it can be done. Can you post an example file in the format you have so we can play about with it? (The FAQ describes how, but it is similar to posting your jpeg files).

Is the data only on row 1 to start with? Do you always have the same number of columns, if in multiple rows?

Pete

Greetings Pete!
Thank you for offering a hand!
Here is a sample file (the first record repeated thrice): Sample_NewRows_8-15-13.xlsx

The data pastes into Excel as multiple rows in one column, but I have transposed it once. If that original format would work better, let me know.

And yes, each record has a stable number of columns, 9. So the first record is A1:I1, second record is J1:R1, etc.

Put this formula in A1 of a new sheet:

=IF(OR(COLUMN(A$1)>9,(ROWS($1:1)-1)*9+COLUMN(A$1)>COUNTA(Sheet4!$1:$1)),"",INDEX(Sheet4!$1:$1,(ROWS($1:1)-1)*9+COLUMN(A$1)))

then you can copy across. Use the Format Painter to copy the formats for the first record from your other sheet, and then you can copy the row down as far as you need to. In the attached file I've copied across to column L and down to row 7 to show that you just get blanks if you copy too far.

Hope this helps.

Pete

Ah, so close.
I see how well this works in the sample.
I had failed to realize that any record labeled "I" (incoming) does not have a phone number associated with it, so there are blanks that I did not see. Some records are actually shorter than others.

You have written a very impressive formula here, Pete, but could I ask if you know another way to detect the actual "D" character to denote the next record?

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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