Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help finding unassigned numbers in a list
I have a large item master list and would like to find numbers that have not
been assigned. Is there a simple formula I can use to pull out numbers not used in a string on numbers? -- nfw |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help finding unassigned numbers in a list
Say we start with:
115 109 105 111 112 117 114 104 106 102 107 100 101 116 118 110 113 103 119 First we sort them: 100 101 102 103 104 105 106 107 109 110 111 112 113 114 115 116 117 118 119 Then in B2 enter: =IF(A2-A1=1,"",A2-A1-1) and copy down. We now see: 100 101 102 103 104 105 106 107 109 1 110 111 112 113 114 115 116 117 118 119 This identifies 108 as the missing value. -- Gary''s Student - gsnu200901 "Nancy" wrote: I have a large item master list and would like to find numbers that have not been assigned. Is there a simple formula I can use to pull out numbers not used in a string on numbers? -- nfw |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help finding unassigned numbers in a list
Could you post a few examples of what you have?
Pete On Aug 21, 4:59*pm, Nancy wrote: I have a large item master list and would like to find numbers that have not been assigned. Is there *a simple formula I can use to pull out numbers not used in a string on numbers? -- nfw |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help finding unassigned numbers in a list
You can use a VBA procedure like the following:
Sub ListMissing() Dim R As Range Dim Dest As Range Dim LastRow As Long Dim N As Long Set R = Range("A1") '<<< cell where numbers start With R.Worksheet LastRow = .Cells(.Rows.Count, R.Column).End(xlUp).Row End With Set Dest = Range("H10") '<<< cell where missing numbers are written Set R = R(2, 1) Do Until R.Row = LastRow For N = R.Value + 1 To R(2, 1).Value - 1 Dest.Value = N Set Dest = Dest(2, 1) Next N Set R = R(2, 1) Loop End Sub Change the lines marked with "<<<" to the approiate cell references. R is the first cell of the master list of numbers. Dest is the cell where the missing numbers are to be written. This code assumes that the numbers in the master list are integers (no fractional portion) and that they are in ascending order. If you want to select the range of cells in the master list, get rid of With R.Worksheet LastRow = .Cells(.Rows.Count, R.Column).End(xlUp).Row End With and replace it with With Selection Set R = .Cells(1, 1) LastRow = .Cells(.Cells.Count).Row End With Then, select a range in the master list (one column, many rows) and run the code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 21 Aug 2009 08:59:01 -0700, Nancy wrote: I have a large item master list and would like to find numbers that have not been assigned. Is there a simple formula I can use to pull out numbers not used in a string on numbers? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help finding unassigned numbers in a list
On Aug 21, 1:59*pm, Nancy wrote:
I have a large item master list and would like to find numbers that have not been assigned. Is there *a simple formula I can use to pull out numbers not used in a string on numbers? -- nfw Copy the following formula down for each number you want to check. The formula will return 1 for each number not used in the string of numbers. I'm assuming that the 'string' of numbers are in one cell. You can then use AutoFilter to extract these numbers. =IF(ISERROR(FIND(E10,$E$4)),1,"") =IF(ISERROR(FIND(string_to_search_for,string_to_se arch_in)),1,"") More FREE Excel Tips at... http://www.TheExcelAddict.com |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help finding unassigned numbers in a list
Copy the following formula down for each number you want to check.
The formula will return 1 for each number not used in the string of numbers. I'm assuming that the 'string' of numbers are in one cell. You can then use AutoFilter to extract these numbers. =IF(ISERROR(FIND(E10,$E$4)),1,"") =IF(ISERROR(FIND(string_to_search_for,string_to_se arch_in)),1,"") More FREE Excel Tips at... http://www.TheExcelAddict.com |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help finding unassigned numbers in a list
So, what you want to do is find missing numbers from a sequence?
Are the numbers simple numbers like 1,2,3,4,5 etc? 1,2,4,5 You want to know that 3 is missing, right? If the numbers are simple numbers *and* the max number in the sequence is no greater than the total rows available in a worksheet then it's fairly simple but it could be a slow to calculate function if the sequence is large and there are a lot of missing numbers. So, we need more detail. -- Biff Microsoft Excel MVP "Nancy" wrote in message ... I have a large item master list and would like to find numbers that have not been assigned. Is there a simple formula I can use to pull out numbers not used in a string on numbers? -- nfw |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding selected numbers within a master list | Excel Worksheet Functions | |||
Finding an average of a list that could have 0 to 10 numbers in it | Excel Discussion (Misc queries) | |||
finding the median from a list of unsorted numbers | Excel Discussion (Misc queries) | |||
Finding a predetermined Sum from a List of Numbers | Excel Discussion (Misc queries) | |||
finding numbers | New Users to Excel |