Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Identify missing record numbers
Hello.
I have a spreadsheet that is used to track unique tracking codes assigned to companies. I am looking for a way to easily identify codes (within a range of 0000-8000) that has not been assigned. I would greatly appreciate help. Thank you! |
#2
|
|||
|
|||
1. Re-create the codes from 0-8000 in A2:A8001 in another
sheet (you can enter 1, 2, and then select them, grab the fill handle, and drag down). Put "Remaining Codes" in A1. 2. In B2 on the same sheet, put: =COUNTIF(Sheet2!A:A,A2) This assumes your range of codes is on Sheet2, col. A. 3. Fill the formula down. 4. Filter for 0 or False on col. B using an AutoFilter (Data Filter AutoFilter) HTH Jason Atlanta, GA -----Original Message----- Hello. I have a spreadsheet that is used to track unique tracking codes assigned to companies. I am looking for a way to easily identify codes (within a range of 0000-8000) that has not been assigned. I would greatly appreciate help. Thank you! . |
#3
|
|||
|
|||
kabobot
I found this code somewhere out there in code-land. Sub DisplayMissing() Dim C As Range, V As Variant Dim prev&, k&, n& k = 1 prev = 10000 For Each C In Intersect(Range("A:A"), ActiveSheet.UsedRange) If C prev + 1 Then V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")") n = C - (prev + 1) Cells(k, "C").Resize(n, 1) = V k = k + n End If prev = C Next C End Sub Lists the missing numbers from column A into Column C Gord Dibben Excel MVP On Tue, 4 Jan 2005 12:01:07 -0800, "kabobot" wrote: Hello. I have a spreadsheet that is used to track unique tracking codes assigned to companies. I am looking for a way to easily identify codes (within a range of 0000-8000) that has not been assigned. I would greatly appreciate help. Thank you! |
#4
|
|||
|
|||
Gord..
I am fairly unfamiliar with macros or VB. Can you point out where this code needs editing. thank you again. "Gord Dibben" wrote: kabobot I found this code somewhere out there in code-land. Sub DisplayMissing() Dim C As Range, V As Variant Dim prev&, k&, n& k = 1 prev = 10000 For Each C In Intersect(Range("A:A"), ActiveSheet.UsedRange) If C prev + 1 Then V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")") n = C - (prev + 1) Cells(k, "C").Resize(n, 1) = V k = k + n End If prev = C Next C End Sub Lists the missing numbers from column A into Column C Gord Dibben Excel MVP On Tue, 4 Jan 2005 12:01:07 -0800, "kabobot" wrote: Hello. I have a spreadsheet that is used to track unique tracking codes assigned to companies. I am looking for a way to easily identify codes (within a range of 0000-8000) that has not been assigned. I would greatly appreciate help. Thank you! |
#5
|
|||
|
|||
kabobot
If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. As far as "editing" the macro goes, what exactly would you like changed? Gord On Tue, 4 Jan 2005 13:29:08 -0800, "kabobot" wrote: Gord.. I am fairly unfamiliar with macros or VB. Can you point out where this code needs editing. thank you again. "Gord Dibben" wrote: kabobot I found this code somewhere out there in code-land. Sub DisplayMissing() Dim C As Range, V As Variant Dim prev&, k&, n& k = 1 prev = 10000 For Each C In Intersect(Range("A:A"), ActiveSheet.UsedRange) If C prev + 1 Then V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")") n = C - (prev + 1) Cells(k, "C").Resize(n, 1) = V k = k + n End If prev = C Next C End Sub Lists the missing numbers from column A into Column C Gord Dibben Excel MVP On Tue, 4 Jan 2005 12:01:07 -0800, "kabobot" wrote: Hello. I have a spreadsheet that is used to track unique tracking codes assigned to companies. I am looking for a way to easily identify codes (within a range of 0000-8000) that has not been assigned. I would greatly appreciate help. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
How do I take two columns of sequential numbers and insert spaces | Excel Discussion (Misc queries) |