Find missing numbers and assign them to a new column

[ammdumas]

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?

 

[venkat1926]

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
If ActiveCell.Address = llastcell.Offset(1, 0).Address Then 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.

 

[ammdumas]

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
	A	B	C	D
125	141		4200-V-141
126	142		4200-V-142
127	143		4200-V-143
128	145	144	4200-V-145
129	146		4200-V-146
130	147		4200-V-147
131	148		4200-V-148
132	151	149	4200-V-151
133	152	150	4200-V-152
134	156	153	4200-V-156
135	158	157	4200-V-158
136	159	155	4200-V-159
137	161	160	4200-V-161
138	163	162	4200-V-163
139	164		4200-V-164
140	166	165	4200-V-166
141	167		4200-V-167
142	168		4200-V-168
143	169		4200-V-169
144	170		4200-V-170
145	172	171	4200-V-172
Sheet1

 

[venkat1926]

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
If ActiveCell.Address = llastcell.Offset(1, 0).Address Then GoTo line1
GoTo line2
line1:
End Sub

the resultant spreadsheet is also give below
*removed by Admin*

 

[Peter_SSs]

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
	A	B	C	D	E	F	G
1	4200-V-001		Available?	Code List		No Available	4200-V-003
2	4200-V-002			4200-V-001		3	4200-V-008
3	4200-V-004			4200-V-002			4200-V-009
4	4200-V-005		Available 1	4200-V-003
5	4200-V-006			4200-V-004
6	4200-V-007			4200-V-005
7	4200-V-010			4200-V-006
8	4200-V-011			4200-V-007
9			Available 2	4200-V-008
10			Available 3	4200-V-009
11				4200-V-010
12				4200-V-011
Sheet1

 

[ammdumas]

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

 

[Peter_SSs]

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

 

[ammdumas]

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.

 

[Peter_SSs]

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
	A	B	C	D	E	F	G
1	4200-V-001		Available?	Code List		No Available	4200-V-003
2	4200-V-002			4200-V-001		8	4200-V-008
3	4200-V-004			4200-V-002			4200-V-009
4	4200-V-005		Available 1	4200-V-003			5100-V-002
5	4200-V-006			4200-V-004			5100-V-004
6	4200-V-007			4200-V-005			6110-V-001
7	4200-V-010			4200-V-006			6110-V-003
8	4200-V-011			4200-V-007			6110-V-005
9	5100-V-001		Available 2	4200-V-008
10	5100-V-003		Available 3	4200-V-009
11	5100-V-005			4200-V-010
12	6110-V-002			4200-V-011
13	6110-V-004			5100-V-001
14			Available 4	5100-V-002
15				5100-V-003
16			Available 5	5100-V-004
17				5100-V-005
18			Available 6	6110-V-001
19				6110-V-002
20			Available 7	6110-V-003
21				6110-V-004
22			Available 8	6110-V-005
Unused Codes

 

[ammdumas]

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