# Find missing numbers and assign them to a new column

#### ammdumas

##### Active Member
Hey guys,

A little tougher questions then just finding missing number and then identifying them. I have a series of codes in a column like such...

4200-V-001
4200-V-002
4200-V-004
4200-V-005
4200-V-006
4200-V-007
4200-V-010
4200-V-011

This code has also been separated into other columns identifying Area (4200), Type (V) and Sequential Number (001).

I need a program that will go through the list, find the missing numbers (in this case 4200-V-003, 4200-V-008 and 4200-V-009) and put them into a new column. I need this so I can identify unused codes. Any ideas?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
If you have not got any more elegant solution this is the one. read carefully

1. I have introduced two columns left of your column that means your codes are in column C
2. for testing pruposes I have added one more cell in column C viz
4200-V-014
3. see the formulas in column A
4. now sort the database in my case columbnsA to C according to column A
5. in the beginning columm B will be blank
6. now run the sub I have given below
code begins
Option Explicit
Dim i As Integer
Dim j As Integer
Dim llastcell As Range
'BEFORES RUNING THE MACRO SORT ASCENDING THE COLUMN
Public Sub test()
Range("a2").Select
line2:
Set llastcell = Cells(Rows.Count, 1).End(xlUp)

i = ActiveCell - ActiveCell.Offset(-1, 0)
If i > 1 Then

For j = 1 To i - 1
ActiveCell.Offset(j - 1, 1) = ActiveCell.Offset(-1, 0) + j
Next
End If
ActiveCell.Offset(1, 0).Select
On Error GoTo line1
GoTo line2
line1:
End Sub
code ends
I have given the sheet BELOW after running the sub. you delete the entries in column B(and NOT column B) and run the macro and see whethre you got what you want . any bug let me know.

Edited by Firefytr, Nov 7, 2005, 1847 hrs, GMT; Reason: HTML Maker post mishap.

Sooooo close! :x

It seems to work fine, except for when it has to list gaps that are close together. Because the code puts the missing numbers in the line immediately below where the gap appears in column B, if there is a gap of more than two with only one number separating the gaps, the code skips/overwrites certain numbers.

Have a look at the section I posted. Number '154' got overwritten by the next gap to be displayed (I.e. number 157). I can guess the missing numbers, but this poses a problem if I have a lot of gaps close together and numbers start getting overlapped and replaced.

I'll slap you with another problem too...is there a way to modify the code so that it can do multiple areas? I.e. 4200-V-'numbers' and 5100-V-'numbers' etc.?
code gap finder.xls
ABCD
1251414200-V-141
1261424200-V-142
1271434200-V-143
1281451444200-V-145
1291464200-V-146
1301474200-V-147
1311484200-V-148
1321511494200-V-151
1331521504200-V-152
1341561534200-V-156
1351581574200-V-158
1361591554200-V-159
1371611604200-V-161
1381631624200-V-163
1391644200-V-164
1401661654200-V-166
1411674200-V-167
1421684200-V-168
1431694200-V-169
1441704200-V-170
1451721714200-V-172
Sheet1

I have slightly modifed the code
mine of course starts from row 1
modify to suit you

the modified code comes in between the comment line '=========

will this be ok.
I am slso ending the code below

Dim i As Integer
Dim j As Integer
Dim llastcell As Range
'BEFORES RUNING THE MACRO SORT ASCENDING THE COLUMN
Public Sub test()
Range("a2").Select
line2:
Set llastcell = Cells(Rows.Count, 1).End(xlUp)

i = ActiveCell - ActiveCell.Offset(-1, 0)
If i > 1 Then

For j = 1 To i - 1
'ActiveCell.Offset(j - 1, 1) = ActiveCell.Offset(-1, 0) + j
'============
ActiveCell.Offset(j - 1, 1).End(xlUp).Offset(1, 0) = ActiveCell.Offset(-1, 0) + j
'=================
Next
End If
ActiveCell.Offset(1, 0).Select
On Error GoTo line1
GoTo line2
line1:
End Sub

the resultant spreadsheet is also give below

Using formulas rather than VBA, here are a couple of ideas to consider.

First idea is to list all the codes down column D. The formula in C2 (copied down) is:
=IF(ISNUMBER(MATCH(D2,\$A\$1:\$A\$12,0)),"","Available "&COUNTIF(C\$1:C1,">A"))

If this is not sufficient, formula in F2 is =COUNTIF(C2:C12,">A") and G1 (copied down) is =IF(ROW()<=\$F\$2,VLOOKUP("Available "&ROW(),\$C\$1:\$D\$12,2,0),"")
Columns C, D F could be hidden if required.
Mr Excel.xls
ABCDEFG
14200-V-001Available?Code ListNo Available4200-V-003
24200-V-002 4200-V-00134200-V-008
34200-V-004 4200-V-0024200-V-009
44200-V-005Available 14200-V-003
54200-V-006 4200-V-004
64200-V-007 4200-V-005
74200-V-010 4200-V-006
84200-V-011 4200-V-007
9Available 24200-V-008
10Available 34200-V-009
11 4200-V-010
12 4200-V-011
Sheet1

Thanks guys, both of your solutions work.

As to the second part of my question...any ways to check multiple series at the same time? I.e....
4200-V-(numbers)
5100-V-(numbers)
6110-V-(numbers), etc.?

ammdumas said:
Thanks guys, both of your solutions work.

As to the second part of my question...any ways to check multiple series at the same time? I.e....
4200-V-(numbers)
5100-V-(numbers)
6110-V-(numbers), etc.?
Where are the other lists of codes in relation to the 4200-V numbers? Mixed in? Multiple adjoining columns? Disjoint columns, same sheet? Different sheets?

And where do the answers go? Mixed in? Separate columns for each set? etc

Same sheet, same column, already sorted. So all the 4200s would be together, all the 5100s would be together, etc.
As I mention before, that code is also separated into its individual parts (4200, V, 001) in other columns as well. I expect this will make the solution easier...

Thanks for the help.

ammdumas

Is this layout and solution any good? I have just extended my previous example and expanded the formula ranges to cope.

Now the formulas are:
C2: =IF(ISNUMBER(MATCH(D2,\$A\$1:\$A\$13,0)),"","Available "&COUNTIF(C\$1:C1,">A"))

F2:=COUNTIF(C2:C22,">A")

G1: =IF(ROW()<=\$F\$2,VLOOKUP("Available "&ROW(),\$C\$1:\$D\$22,2,0),"")
Mr Excel.xls
ABCDEFG
14200-V-001Available?Code ListNo Available4200-V-003
24200-V-002 4200-V-00184200-V-008
34200-V-004 4200-V-0024200-V-009
44200-V-005Available 14200-V-0035100-V-002
54200-V-006 4200-V-0045100-V-004
64200-V-007 4200-V-0056110-V-001
74200-V-010 4200-V-0066110-V-003
84200-V-011 4200-V-0076110-V-005
95100-V-001Available 24200-V-008
105100-V-003Available 34200-V-009
115100-V-005 4200-V-010
126110-V-002 4200-V-011
136110-V-004 5100-V-001
14Available 45100-V-002
15 5100-V-003
16Available 55100-V-004
17 5100-V-005
18Available 66110-V-001
19 6110-V-002
20Available 76110-V-003
21 6110-V-004
22Available 86110-V-005
Unused Codes

Works for me! Thanks a bunch. Both of you. ray:

Replies
4
Views
140
Replies
3
Views
179
Replies
0
Views
222
Replies
0
Views
100
Replies
3
Views
3K

1,196,254
Messages
6,014,269
Members
441,810
Latest member
LouLou1234

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back