Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do i identify missing numbers in a sequential list
I have a large list of serial numbers like (1AW15648). I need to identify
numbers that are missing. Thanks, Chet Sheetz PS I am using Excel 2003 |
#2
|
|||
|
|||
Chet,
For a list starting in cell A1, you could use something like =IF((VALUE(RIGHT(A2,5))-1)=VALUE(RIGHT(A1,5)),"","Skipped") copied down to match your list of SORTED serial numbers. This assumes that your numbers of interest are the 5 right-most digits. HTH, Bernie MS Excel MVP "Chet-a-roo" wrote in message ... I have a large list of serial numbers like (1AW15648). I need to identify numbers that are missing. Thanks, Chet Sheetz PS I am using Excel 2003 |
#3
|
|||
|
|||
You might be able to use something like this:
=IF(RIGHT(A3,5)<RIGHT(A2,5)+1,"Missing Number","") assuming that the numeric part is always the last 5 characters Sheila "Chet-a-roo" wrote: I have a large list of serial numbers like (1AW15648). I need to identify numbers that are missing. Thanks, Chet Sheetz PS I am using Excel 2003 |
#4
|
|||
|
|||
Hi Chet I once had a similar problem where I had to match two lists to see which was missing. In my case I had to insert lines in the original list so the two lists matched. eg A B 1 1 2 2 3 3 5 4 6 5 The code I used moved down column A to match the two values. Try this, you will have to adapt it for you own job. Sub Matchem() LastRow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row Range("C1").Select For j = 1 To LastRow If Range("a" & j) < Range("b" & j) Then Range("a" & j).Select Selection.Insert Shift:=xlDown Range("c" & j).Select Next j End Sub -- Colin Vicary ------------------------------------------------------------------------ Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472 View this thread: http://www.excelforum.com/showthread...hreadid=393346 |
#5
|
|||
|
|||
Hi Chet,
The following two approaches may work. First arrange your data in ascending order. Let's say you have 900 values, and that the first value is 1AW00001 and it is in A1, and the last value is 1AW01000 and it is in A900; so there are 100 missing values) Create a helper column (say B) with consecutive numbers (1AW00001 in B1, 1AW00002 in B2,......1AW01000 in B1000). Note that this column would be longer since it contains ALL the values in your range including the missing values. Approach 1 (Using Conditional Formatting) Highlight B2, go to 'Conditional Formatting' in the Format menu; Select "Formula Is" and enter the formula, =ISNUMBER(MATCH(B1,$A$1:$A$900,0))+1=1 and choose an appropriate formatting (font type, font color, or cell shading) to easily identify the missing values. And of course, extend that conditional formatting criterion (you could use conditional formatting painter) to the entire column B. This should identify all the missing values. Approach 2: In this approach you need another helper column (say C). In C1 enter the formula, =IF(ISNUMBER(MATCH(B1,$A$1:$A$900,0))," ",B1) and fill-in the formula down to C1000. Hope one of these works for you. Regards, B.R. Ramachandran "Chet-a-roo" wrote: I have a large list of serial numbers like (1AW15648). I need to identify numbers that are missing. Thanks, Chet Sheetz PS I am using Excel 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding numbers missing from a sequence | Excel Discussion (Misc queries) | |||
How do I assign sequential numbers in an Excel 2003 PO template? | Excel Worksheet Functions | |||
identify numbers which are listed in two columns. | Excel Worksheet Functions | |||
Identify missing record numbers | Excel Discussion (Misc queries) | |||
How do I take two columns of sequential numbers and insert spaces | Excel Discussion (Misc queries) |