+ Reply to Thread
Results 1 to 7 of 7

Insert blank row if cell value sequence is incorrect. e.g., if A, C, then insert row?

  1. #1
    Registered User
    Join Date
    01-28-2023
    Location
    Canada
    MS-Off Ver
    365
    Posts
    4

    Insert blank row if cell value sequence is incorrect. e.g., if A, C, then insert row?

    Hi Everyone,
    I'm importing data from a MS-DOS era database txt file export. There are 17000 records with 19 possible data fields each. However, if a field wasn't populated in the DOS database, the export doesn't include a space for it in the export, which means the order of the fields vary, and therefore can't be transposed. One record might have A, B, C, D fields, and the next one has A, C, D (I.e., B is missing)

    Does anyone know how I could accomplish something like "If a cell with the value A is not followed by a cell with the value B, insert blank row"?

    Here is an example of the first four lines from two records:

    TN
    CN
    TI
    G

    and

    TN
    CN
    ST
    TI

    In this example, the first record is missing a field called "ST", which should be after CN. I'd like run a command that says "If CN is not followed by ST, insert blank row"
    THANK YOU!
    Last edited by gaelen; 01-28-2023 at 11:21 PM.

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Insert blank row if cell value sequence is incorrect. e.g., if A, C, then insert row?

    Are you looking for a VBA solution?
    You may also want to attach a sample file - please read the yellow banner at the top of the page.

  3. #3
    Registered User
    Join Date
    01-28-2023
    Location
    Canada
    MS-Off Ver
    365
    Posts
    4

    Re: Insert blank row if cell value sequence is incorrect. e.g., if A, C, then insert row?

    Thanks for the reply! Yes to a VBA ( I just learned that apparently only VBAs can insert rows, and formulas can't).
    Attached Files Attached Files
    Last edited by gaelen; 01-28-2023 at 11:59 PM.

  4. #4
    Registered User
    Join Date
    01-28-2023
    Location
    Canada
    MS-Off Ver
    365
    Posts
    4

    Re: Insert blank row if cell value sequence is incorrect. e.g., if A, C, then insert row?

    Putting the question simply, imagine there was a column that should be the whole alphabet, but some letters are missing. Might there be a formula or VBA to insert a blank row where relevant the sequence is incorrect? I have found macros for numerical sequences, but not for something arbitrary like CN should follow TN.
    Last edited by gaelen; 01-28-2023 at 11:57 PM.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Insert blank row if cell value sequence is incorrect. e.g., if A, C, then insert row?

    Macro code:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 01-29-2023 at 03:24 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    01-28-2023
    Location
    Canada
    MS-Off Ver
    365
    Posts
    4
    Quote Originally Posted by kvsrinivasamurthy View Post
    Macro code:
    Please Login or Register  to view this content.
    Thank you so much for your help and time!

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Insert blank row if cell value sequence is incorrect. e.g., if A, C, then insert row?

    Thanks for feedback.

+ 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. Replies: 2
    Last Post: 10-28-2017, 10:51 AM
  2. Insert a blank row in incomplete sequence
    By Moriexcel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-01-2016, 03:24 AM
  3. Table that insert measures and insert blank rows
    By searme in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2016, 04:49 AM
  4. insert a blank row for a missing value of a repeated sequence
    By newexcel123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2014, 01:58 PM
  5. Insert a blank row if number sequence is not complete.
    By califorlina in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-11-2013, 08:59 AM
  6. [SOLVED] Macro to copy row, insert below, invert amt, and insert blank row between each new tx
    By raquel1227 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-07-2013, 12:56 PM
  7. Replies: 2
    Last Post: 11-29-2011, 06:26 AM

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