How to insert new row with dummy info if there is no sequence to item numbers

[pyrte]

Hi there,

I'm working on finding out missing orders in a bunch of sales records. I'm attaching a sample workbook here so that you can understand more of my requirements. Appreciate all the help you can provide.

The sales invoice denotes the sales invoice number at a material level. The item number denotes the line items in the sales invoice. If the sequence is broken then an item was deleted. I need a way to either through formula or macro to be able to find out these missing values, then add a row and fill it with the word deleted record. The item number is in an increment of 10 always. Each sales order item number will start with 10 with an increment of 10.

Looking forward to you guys help and support.

Sales Invoice Item no Material Customer 1212121 10 125a1211 A 1212121 20 85a421 A 1212121 30 41a5a5 A 1212121 40 a45a15 A 1212121 50 1a54a5 A 2222131 10 a452a74 B 2222131 20 125a1212 B 2222131 40 85a422 B 2222131 50 41a5a6 B 4512432 10 a45a16 C 4512432 20 1a54a6 C 4512432 30 a452a75 C 4512432 50 125a1213 C 4512432 60 85a423 C 4512432 80 41a5a7 C 4512432 90 a45a17 C 8747451 10 1a54a7 D 8747451 20 a452a76 D 8747451 30 125a1214 D 8747451 40 85a424 D 8747451 50 41a5a8 D 8747451 60 a45a18 D <colgroup><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[oldbrewer]

Sales Invoice	Item no	Material	Customer	MARKER
1212121	10	125a1211	A
1212121	20	85a421	A		#######
1212121	30	41a5a5	A
1212121	40	a45a15	A
1212121	50	1a54a5	A
2222131	10	a452a74	B
2222131	20	125a1212	B
2222131	40	85a422	B	M			now all you need to do is run down col E and insert blank row where the M's are
2222131	50	41a5a6	B
4512432	10	a45a16	C				I will post again with that macro in 5 minutes
4512432	20	1a54a6	C
4512432	30	a452a75	C
4512432	50	125a1213	C	M
4512432	60	85a423	C
4512432	80	41a5a7	C	M
4512432	90	a45a17	C
8747451	10	1a54a7	D
8747451	20	a452a76	D
8747451	30	125a1214	D
8747451	40	85a424	D
8747451	50	41a5a8	D
8747451	60	a45a18	D
	#######
	=IF(AND(A3=A2,B3-B2<>10),"M","")

<colgroup><col><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG20Jul34
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
[COLOR="Navy"]With[/COLOR] Range("A" & n)
[COLOR="Navy"]If[/COLOR] Not .Offset(1) = "" And .Offset(1) = .Value [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .Offset(1, 1).Value + 10 = .Offset(, 1) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] (.Offset(1, 1).Value - .Offset(, 1)) / 10 > 1 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] R = .Offset(1).Resize(((.Offset(1, 1).Value - .Offset(, 1)) / 10) - 1)
            R.EntireRow.Insert
            R.Offset(-R.Rows.Count).Value = "Deleted Record"
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[oldbrewer]

Sales Invoice	Item no	Material	Customer	MARKER
1212121	10	125a1211	A
1212121	20	85a421	A		#######
1212121	30	41a5a5	A
1212121	40	a45a15	A
1212121	50	1a54a5	A
2222131	10	a452a74	B
2222131	20	125a1212	B
2222131	40	85a422	B	M			now all you need to do is run down col E and insert blank row where the M's are
2222131	50	41a5a6	B
4512432	10	a45a16	C				I will post again with that macro in 5 minutes
4512432	20	1a54a6	C
4512432	30	a452a75	C
							For j = 3 To 100
4512432	50	125a1213	C	M			If Cells(j, 4) = "" Then GoTo 100
4512432	60	85a423	C				If Cells(j, 5) = "M" Then GoTo 50 Else GoTo 75
							50 Cells(j, 5).Select
4512432	80	41a5a7	C	M			Selection.EntireRow.Insert
4512432	90	a45a17	C				j = j + 1
8747451	10	1a54a7	D				75 Next j
8747451	20	a452a76	D				100 End Sub
8747451	30	125a1214	D
8747451	40	85a424	D
8747451	50	41a5a8	D
8747451	60	a45a18	D
							easy to add lines into macro that put "deleted record into the new blank row
	#######
	=IF(AND(A3=A2,B3-B2<>10),"M","")

<colgroup><col><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[oldbrewer]

Sales Invoice

Item no

Material

Customer

MARKER

1212121

10

125a1211

A

1212121

20

85a421

A

#######

1212121

30

41a5a5

A

1212121

40

a45a15

A

1212121

50

1a54a5

A

2222131

10

a452a74

B

2222131

20

125a1212

B

THIS RECORD HAS BEEN DELETED

2222131

40

85a422

B

M

now all you need to do is run down col E and insert blank row where the M's are

2222131

50

41a5a6

B

4512432

10

a45a16

C

I will post again with that macro in 5 minutes

4512432

20

1a54a6

C

4512432

30

a452a75

C

THIS RECORD HAS BEEN DELETED

FINAL VERSION

4512432

50

125a1213

C

M

4512432

60

85a423

C

For j = 3 To 100

THIS RECORD HAS BEEN DELETED

If Cells(j, 4) = "" Then GoTo 100

4512432

80

41a5a7

C

M

If Cells(j, 5) = "M" Then GoTo 50 Else GoTo 75

4512432

90

a45a17

C

50 Cells(j, 5).Select

8747451

10

1a54a7

D

Selection.EntireRow.Insert

8747451

20

a452a76

D

Cells(j, 1) = "THIS RECORD HAS BEEN DELETED"

8747451

30

125a1214

D

j = j + 1

8747451

40

85a424

D

75 Next j

8747451

50

41a5a8

D

100 End Sub

8747451

60

a45a18

D

#######

=IF(AND(A3=A2,B3-B2<>10),"M","")

DELETED TEXT IS ON ONE ROW - HONEST

<tbody>
</tbody>

 

[pyrte]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG20Jul34
[COLOR=Navy]Dim[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Num [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] R [COLOR=Navy]As[/COLOR] Range
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR=Navy]For[/COLOR] n = Lst To 2 [COLOR=Navy]Step[/COLOR] -1
[COLOR=Navy]With[/COLOR] Range("A" & n)
[COLOR=Navy]If[/COLOR] Not .Offset(1) = "" And .Offset(1) = .Value [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]If[/COLOR] Not .Offset(1, 1).Value + 10 = .Offset(, 1) [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] (.Offset(1, 1).Value - .Offset(, 1)) / 10 > 1 [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]Set[/COLOR] R = .Offset(1).Resize(((.Offset(1, 1).Value - .Offset(, 1)) / 10) - 1)
            R.EntireRow.Insert
            R.Offset(-R.Rows.Count).Value = "Deleted Record"
        [COLOR=Navy]End[/COLOR] If
   [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

This worked like a charm. Thank you so much for your support. I was really worried how I was going to handle something like for over 10 million records. You just saved me a whole deal of pain. You are simple awesome. I truly appreciate your help.

 

[MickG]

You're very welcome

 

[Ombir]

Hi,

Here is another alternative.

Sub pyrte()
Dim i   As Long

For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) Then
       If (Cells(i, 2) - Cells(i - 1, 2)) / 10 > 1 Then
            Cells(i, 1).EntireRow.insert
            Cells(i, 1) = "Deleted Record"
        End If
    End If
Next
End Sub

 

[oldbrewer]

mine is only 9 lines of code (10 if you need a name for the sub)

 

[pyrte]

Thanks so much oldbrewer, Ombir and MickG. You all have been awesome support. I've learned so much and also solved a problem of having to review of over 1 million records.