Forum Discussion

gaelen's avatar
gaelen
Copper Contributor
Jan 29, 2023

Insert blank row if cell value sequence is incorrect. e.g., if A, C, then insert row between them [f

Hi Everyone,

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"?

 

I'm importing data from a MS-DOS era database txt file export. There are 17000 records with 15 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).

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!

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 29, 2023

    gaelen 

    Sub datafields()

Dim i, j, k, m As Long
m = 2
k = Cells(2, Columns.Count).End(xlToLeft).Column
For j = 4 To k

For i = 2 To 15
If Cells(i, j).Value <> Cells(m, 1).Value Then
Cells(i, j).Insert
m = m + 1
Else
m = m + 1
End If
If m = 16 Then
m = 2
Else
End If
Next i
Next j

End Sub

    Maybe with these lines of code. The full data field is in range A2:A16 in the example and 23 sample records are in columns D to Z. You can click the button in cell B2 in the attached file to run the macro which adds a blank cell for every missing data field item in every record. The macro dynamically identifies the number of records in the sheet.

Resources