+ Reply to Thread
Results 1 to 10 of 10

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

Hybrid View

  1. #1
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,527

    Re: 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

  2. #2
    Registered User
    Join Date
    08-15-2013
    Location
    Greenfield, MA, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,527

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

    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: copy to clipboard
    =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: copy to clipboard
    =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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-15-2013
    Location
    Greenfield, MA, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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!

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,527

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. nth instance of a character in a string
    By AndrewMac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2013, 11:20 AM
  2. [SOLVED] How to Susbtitute all instance(s) of the selected character in any cell using Formulas/VBA
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2012, 01:45 PM
  3. Find nth instance of a character in a string
    By BRISBANEBOB in forum Excel General
    Replies: 1
    Last Post: 12-26-2011, 07:09 PM
  4. Text to columns - First Instance of =
    By hyyfte in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2007, 12:50 PM
  5. Find nth instance of a character in a string
    By Francis Hayes (The Excel Addic in forum Excel General
    Replies: 7
    Last Post: 01-21-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1