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

[gaelen]

Hi Everyone,
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)

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 A20 0015
CN A20 0015
TI World Congress fall celebration, 1991
G [videorecording]

and

TN A21 0001.2
CN A21 0001; AC2 177
ST Assembly
TI New Year's Continental Assembly

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!

 

[gaelen]

Here is a simpler way of representing the example:

TN
CN
TI
G

and

TN
CN
ST
TI

 

[Peter_SSs]

Welcome to the MrExcel board!

Is TN A20 0015 all in one cell, two cells or three cells? (Suggest you investigate XL2BB for providing sample data)

Is there any blank line(s) between records?

 

[gaelen]

Welcome to the MrExcel board!

Is TN A20 0015 all in one cell, two cells or three cells? (Suggest you investigate XL2BB for providing sample data)

Is there any blank line(s) between records?

Thanks for replying Peter.
The current delimiter between records is $.
Here is some sample data. Please let me know if anything seems unclear.

Sample Data Fields Sequence.xlsx
	A	B	C	D	E	F
1	Current Irregular sequence ($ is the record Delimiter)		Desired Sequence			What I'd like Column A to look like after formula or VBA
2	TN		TN			TN
3	CN		CN			CN
4	TI		ST			TI
5	G		SS
6	SP		TI
7	ES		OT
8	DR		G			G
9	PR		SP			SP
10	PT		ES			ES
11	PD		DR			DR
12	CH		PR			PR
13	CD		PT			PT
14	RC		PD			PD
15	DE		CH			CH
16	K		CD			CD
17	MP		RC			RC
18	$		DE			DE
19	TN		K			K
20	CN		MP			MP
21	ST					$
22	SS
23	TI
24	OT
25	G
26	SP
27	ES
28	DR
29	PR
30	PT
31	PD
32	CH
33	CD
34	RC
35	DE
36	K
37	MP
38	$
39	TN
40	CN
41	ST
42	SS
43	TI
44	G
45	SP
46	ES
47	DR
48	PR
49	PT
50	PD
51	CH
52	CD
53	RC
54	DE
55	K
56	MP
57	$
58	TN
59	CN
60	ST
61	SS
62	TI
63	OT
64	G
65	SP
66	ES
67	DR
68	PR
69	PT
70	PD
71	CH
72	CD
73	RC
74	DE
75	K
76	MP
77	$
78	TN
79	CN
80	ST
81	SS
82	TI
83	G
84	SP
85	ES
86	DR
87	PR
88	PT
89	PD
90	CH
91	CD
92	RC
93	DE
94	K
95	MP
96	$
Sheet1

 

[gaelen]

(Column F would continue down paster the delimiter)

 

[Peter_SSs]

Thanks for the XL2BB sample data.

Please let me know if anything seems unclear.

Yes, "What I'd like" in column F is unclear. Why isn't it like column G below instead? Or was "TI" in F4 instead of F6 a mistake?

Also, how many columns of data are there in each row with your real data?
Is there actually a heading row with the original data?
Rather than physically inserting rows where required, would it be acceptable to simply produce the data in another worksheet with the appropriate layout?

gaelen.xlsm
	A	B	C	D	E	F	G
1	Current		Desired			What I'd like
2	TN		TN			TN	TN
3	CN		CN			CN	CN
4	TI		ST			TI
5	G		SS
6	SP		TI				TI
7	ES		OT
8	DR		G			G	G
9	PR		SP			SP	SP
10	PT		ES			ES	ES
11	PD		DR			DR	DR
12	CH		PR			PR	PR
13	CD		PT			PT	PT
14	RC		PD			PD	PD
15	DE		CH			CH	CH
16	K		CD			CD	CD
17	MP		RC			RC	RC
18	$		DE			DE	DE
19	TN		K			K	K
20	CN		MP			MP	MP
21	ST					$	$
Sheet1

 

[gaelen]

Thanks for the XL2BB sample data.

Yes, "What I'd like" in column F is unclear. Why isn't it like column G below instead? Or was "TI" in F4 instead of F6 a mistake?

Also, how many columns of data are there in each row with your real data?
Is there actually a heading row with the original data?
Rather than physically inserting rows where required, would it be acceptable to simply produce the data in another worksheet with the appropriate layout?

gaelen.xlsm
	A	B	C	D	E	F	G
1	Current		Desired			What I'd like
2	TN		TN			TN	TN
3	CN		CN			CN	CN
4	TI		ST			TI
5	G		SS
6	SP		TI				TI
7	ES		OT
8	DR		G			G	G
9	PR		SP			SP	SP
10	PT		ES			ES	ES
11	PD		DR			DR	DR
12	CH		PR			PR	PR
13	CD		PT			PT	PT
14	RC		PD			PD	PD
15	DE		CH			CH	CH
16	K		CD			CD	CD
17	MP		RC			RC	RC
18	$		DE			DE	DE
19	TN		K			K	K
20	CN		MP			MP	MP
21	ST					$	$
Sheet1

Good catch - my mistake on F4 (I'm getting quite groggy from the hours of excel) : )
In my real data at the moment I have 1 column with 295K rows (which is a BIG improvement over how the data started). The final table there will be 19 columns with 17000 rows.

Regarding headers, the column I shared will be transposed and the two letter abbreviation data will become the header... ie, I'll split "TN A20 0015" with fixed delimiters for the first two characters, and then transpose the data.... my problem at the moment is that order of the data is inconsistent, so I can't just transpose the 295K rows... unless there is something I'm missing.

All that aside, thank you very much for your replies. I'm 99% done with the document - this is just the last hurdle and I'm fried : ) I'm all ears to any suggestions : )

 

[gaelen]

Good catch - my mistake on F4 (I'm getting quite groggy from the hours of excel) : )
In my real data at the moment I have 1 column with 295K rows (which is a BIG improvement over how the data started). The final table there will be 19 columns with 17000 rows.

Regarding headers, the column I shared will be transposed and the two letter abbreviation data will become the header... ie, I'll split "TN A20 0015" with fixed delimiters for the first two characters, and then transpose the data.... my problem at the moment is that order of the data is inconsistent, so I can't just transpose the 295K rows... unless there is something I'm missing.

All that aside, thank you very much for your replies. I'm 99% done with the document - this is just the last hurdle and I'm fried : ) I'm all ears to any suggestions : )

(when I wrote "the data is inconsistent" I mean the order of the data is inconsistent.... it's not as though they all say title>author>publisher>date (if they did I'd know what to do)... what I have is more like:

title> author>date
author>publisher>date

and I'm unsure of how to create blanks en masse where the empty missing fields should be.

 

[Peter_SSs]

In my real data at the moment I have 1 column with 295K rows (which is a BIG improvement over how the data started). The final table there will be 19 columns with 17000 rows.

Now I am confused whether I am dealing with 1 column or 19?

Regarding headers, the column I shared will be transposed and the two letter abbreviation data will become the header... ie, I'll split "TN A20 0015"

I also do not understand that. Your sample data and expected results in post 4 have nothing to do with "TN A20 0015"

it's not as though they all say title>author>publisher>date

Again, that seems to have nothing to do with the earlier data & what you show us is all we know about your data.

So, lets start somewhere & do a little test.
I am assuming that there is a header row and that the "fields" start in row 2.
I am also assuming for now 2 columns of data.
So my original data looks like columns A:B below.

The following code has produced what you see in columns D:E
Is that headed in the right direction?

Sub FixLayout()
  Dim a As Variant, b As Variant, Fields As Variant
  Dim i As Long, j As Long, f As Long, k As Long, uba2 As Long, ubf As Long
  
  Fields = Split("TN|CN|ST|SS|TI|OT|G|SP|ES|DR|PR|PT|PD|CH|CD|RC|DE|K|MP|$", "|")
  ubf = UBound(Fields)
  a = Range("A1").CurrentRegion
  uba2 = UBound(a, 2)
  ReDim b(1 To Rows.Count, 1 To uba2)
  f = -1
  For i = 2 To UBound(a)
    Do
      f = f + 1
      k = k + 1
    Loop Until a(i, 1) = Fields(f) Or f = ubf
    For j = 1 To uba2
      b(k, j) = a(i, j)
    Next j
    If f = ubf Then f = -1
  Next i
  Range("D2:E2").Resize(k).Value = b
End Sub

gaelen.xlsm
	A	B	C	D	E
1	Hdr1	Hdr2
2	TN	a		TN	a
3	CN	b		CN	b
4	TI	c
5	G	d
6	SP	e		TI	c
7	ES	f
8	DR	g		G	d
9	PR	h		SP	e
10	PT	i		ES	f
11	PD	j		DR	g
12	CH	k		PR	h
13	CD	l		PT	i
14	RC	m		PD	j
15	DE	n		CH	k
16	K	o		CD	l
17	MP	p		RC	m
18	$			DE	n
19	TN	r		K	o
20	CN	s		MP	p
21	ST	t		$
22	SS	u		TN	r
23	TI	v		CN	s
24	OT	w		ST	t
25	G	x		SS	u
26	SP	y		TI	v
27	ES	z		OT	w
28	DR	aa		G	x
29	PR	ab		SP	y
30	PT	ac		ES	z
31	PD	ad		DR	aa
32	CH	ae		PR	ab
33	CD	af		PT	ac
34	RC	ag		PD	ad
35	DE	ah		CH	ae
36	K	ai		CD	af
37	MP	aj		RC	ag
38	$			DE	ah
39	TN	al		K	ai
40	CN	am		MP	aj
41	ST	an		$
42	SS	ao		TN	al
43	TI	ap		CN	am
44	G	aq		ST	an
45	SP	ar		SS	ao
46	ES	as		TI	ap
47	DR	at
48	PR	au		G	aq
49	PT	av		SP	ar
50	PD	aw		ES	as
51	CH	ax		DR	at
52	CD	ay		PR	au
53	RC	az		PT	av
54	DE	a		PD	aw
55	K	b		CH	ax
56	MP	c		CD	ay
57	$			RC	az
58	TN	e		DE	a
59	CN	f		K	b
60	ST	g		MP	c
61	SS	h		$
62	TI	i		TN	e
63	OT	j		CN	f
64	G	k		ST	g
65	SP	l		SS	h
66	ES	m		TI	i
67	DR	n		OT	j
68	PR	o		G	k
69	PT	p		SP	l
70	PD	q		ES	m
71	CH	r		DR	n
72	CD	s		PR	o
73	RC	t		PT	p
74	DE	u		PD	q
75	K	v		CH	r
76	MP	w		CD	s
77	$			RC	t
78	TN	y		DE	u
79	CN	z		K	v
80	ST	aa		MP	w
81	SS	ab		$
82	TI	ac		TN	y
83	G	ad		CN	z
84	SP	ae		ST	aa
85	ES	af		SS	ab
86	DR	ag		TI	ac
87	PR	ah
88	PT	ai		G	ad
89	PD	aj		SP	ae
90	CH	ak		ES	af
91	CD	al		DR	ag
92	RC	am		PR	ah
93	DE	an		PT	ai
94	K	ao		PD	aj
95	MP	ap		CH	ak
96	$			CD	al
97				RC	am
98				DE	an
99				K	ao
100				MP	ap
101				$
Sheet1

 

[gaelen]

Thanks again for your time, Peter - my apologies for the ambiguity. I figured out a manual workaround and was able sort the full set in about three hours. I can explain the workaround here, if you wish, but I doubt it will be useful for future readers.
All the best!