Excel lists, lookup, values????

[rjblanch]

Hi all,

I am looking for a way that I can work out a group of numbers from a list with a start and end point.

This is my issue - I have a list of numbers that I download every night from our phone system. The values start at 1000 and finish at 1999. Not all of the values are included on this list as they may not be allocated to a staff member. What i need to do is somehow work out a way that i can:
1) Pick how many values i want (input a number into a cell is probably the best way. Lets call this value "A")
2) Find a way to list the unallocated values from the original listing. i.e find missing phone numbers.
3) Have the listing copy "A" amount of numbers so that I can paste into a spreadsheet.

I have found ways to show what numbers are missing from the first table, but this does not give me an easy way to automate the copy process as i have to delete blank cells down the list.....

Any ideas greatly appreciated

 

[000Steve000]

If I have understood the requirement correctly, then this macro could be a starter for 10!

 Sub getnos()
Dim RowCount As Integer
Dim i As Integer
Dim PhoneCol As Integer
Dim StartLine As Integer
Dim NoOfLines As Integer
Dim StaffCol As Integer
PhoneCol = 1 ' where the phone numbers are stored
NoOfLines = 1000 ' number of lines to check
StartLine = 1 ' Position of top line
StaffCol = 2 ' Column where allocated names are stored
SpareNumbersSheet = "Sheet2" ' where to put list of spare numbers
'delete old sparenumbers
Worksheets(SpareNumbersSheet).Columns("A:A").Delete Shift:=xlToLeft
'Detect and read in new numbers
For i = StartLine To StartLine + NoOfLines
    If Cells(i, StaffCol) = "" Then
        RowCount = RowCount + 1
        Worksheets(SpareNumbersSheet).Cells(RowCount, 1) = Cells(i, PhoneCol)
    End If
Next i
End Sub

Hope this helps!

 

[SkipVought]

Hi,

Since this is the MS Office forum and not the VBA forum...

Why not

1. sequentially number your rows of data

2. using the column where you have "what numbers are missing from the first table" create a formula to identify what rows to delete

3. sort on the new column

4. delete the rows

5. sort on the sequential number column



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[rjblanch]

Thanks for both of the answers.

I have tried the first method (000Steve000) and this seems to give the best answer yet, but due to having not all numbers with names, we may find this method a little incorrect.

SkipVought, your idea is correct and this is the way we have been doing it in the past, but we were looking for a way that requires no intervention from our part.

Thank you both for the responses.

 

[byundt]

If your assigned numbers are in A1:A2000 and cell C1 contains A (the number of desired values), then the following array formula will return the lowest n unallocated numbers--in order. Enter it in any cell on row 1:
=IF(ROW()<=$C$1,SMALL(IF(COUNTIF(A$1:A$2000,ROW(INDIRECT("1000:1999")))=0,ROW(INDIRECT("1000:1999")),3000),ROW()),"") Array formula, so hold Control + Shift down while hitting Enter. Excel will respond by adding curly braces { } surrounding the equation.

Copy this equation down to the maximum number of desired unallocated numbers. It will fill with blanks when you have satisfied the requirement for A unallocated numbers.