Finding numbers not in a range? 2

[knifey]

Hello,
Can anyone advise the best method to find what numbers are not contained in a column or named range?
I have numbered each row from an input file and am expecting around 500 rows. But only 250 come through.
Numbers in each row of column A read ..147,148,218,222,etc.
Is there a way of telling what row numbers are missing in column A?
Thanks,
Knifey (London)

 

[mintjulep]

If you have a column that contains the numbers that you expect then a COUNTIF statement will do the job with native excel functions.

 

[knifey]

Hello again,
I'll explain in a bit more detail. I am building a system in Excel 2000 to convert Excel files in various formats to one format. But first they need to be reconciled against codes I have in a hidden sheet (very short project outline).
I have 3 sheets, data, adjustments and discrepancies.
If the main code number in the input file row isn't found on my hidden sheet it is a discrepancy.
If a certain string is found in the input file row it is an adjustment.
If the main code number is found on my hidden sheet then it uses offset in my hidden sheet to match a further 3 strings against the input file.
If all match the row goes to the data sheet.
I now have the 3 sheets and shound have about 480 rows in the data sheet (but 250 are missing). I have set column A in the data sheet as the row number of the input file so I can tell what rows are missing.
I realise that the missing rows in the data sheet are because the input row strings are not matched after the main code. I'd just like to make my life easier debugging my code. Espescially as next I have a file with 36,000 rows which I'd rather not eyeball for missing row numbers if I can help it!
All I need now is a way to tell what row numbers are not in column A.
Any help will be greatly appreciated.
Cheers,
Knifey
P.S. Skip, PHV, where are you?

 

[knifey]

Hi mintjulep,
I don't know that much about the countif function. I know I can use it to count occurances of criteria in a range. But Please can you let me know how it would be used in my situation? I need to know the exact row numbers that are missing from column A (I know the total of what is missing already).
I could probably do this with a loop to extract the input file row number of a row that matches the main code but fails the 3 strings, then pass it to a temporary sheet.
But surely there must be a better way than that?
Thanks,
Knifey
P.S. I can provide code, lots and lots of code!




espescially as I don't know how many rows are expected.

 

[SkipVought]

Please post some sample data. A pic is worth K words.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

I don't know that much about the countif function. I know I can use it to count occurances of criteria in a range. But Please can you let me know how it would be used in my situation? I need to know the exact row numbers that are missing from column A (I know the total of what is missing already).

You have a list of the row numbers that should appear, on a separate sheet.
Beside each of these use the countif function to count how many times that number appears in column A of your data. A result of 0 means that row is missing, 1 means that the data is present, more than 1 means you have a duplicate.

Gavin

 

[Bong]

I gather you have a range of numbers (say 1 through 500) that you know to expect. Otherwise, it's really quite impossible to tell which numbers out of an infinite number are not there.

Sub test()
    On Error Resume Next
    a = "dummy"
    For i = 1 To 500
        a = [a1:a25].Find(i, , , True).Address
        If Not a = "dummy" Then
            Debug.Print a
            a = "dummy"
        Else
            Debug.Print i & " not found"
        End If
    Next
End Sub

Where I've arbitrarily chosen A1 through A25 as the search range.

_________________
Bob Rashkin

 

[SkipVought]

No VBA needed!

On another sheet, make a list from 1 to 500.

Use the MATCH function in the adjacent column, to "find" each of the 500.

Where an #N/A is returned, you are missing that number.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[knifey]

Sorry about the delay everyone. I've just got back from holiday.
I ended up going with the MATCH and COUNTIF option on a temporary sheet. Thanks for the initial advice Gavin, and to Skip for explaining it perfectly.
I've posted my code as I think it could be useful to others. I'd imagine knowing what rows are missing (rather than how many) is a great starting point to debugging your code.
Cheers,
Knifey

'this sub finds what row numbers are missing.
'wBook2 is the workbookname where the raw data is found.
'iSLASheet is the sheetname where the raw data is found.
'wb2endRow is the last row number of the raw data.
Sub findMissingRows(ByVal wBook2 As Workbook, iSLASheet As String, wb2endRow As Long)

Dim c As Range

Dim cRange As Range

Dim pRange As Range

Dim wBook1 As Workbook

Application.ScreenUpdating = False

Set wBook1 = ThisWorkbook

With wBook1.Worksheets

If Not SheetExists("tempMissingRows") = True Then

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "tempMissingRows"

Else

wBook1.Worksheets("tempMissingRows").Activate

End If

End With

Set pRange = wBook1.Worksheets("tempMissingRows").Columns("A").Rows("2:" & wb2endRow)

wBook1.Worksheets("tempMissingRows").Range("A1").Value = "Input Row Number"

wBook1.Worksheets("tempMissingRows").Range("B1").Value = "Data"

wBook1.Worksheets("tempMissingRows").Range("C1").Value = "Adjustments"

wBook1.Worksheets("tempMissingRows").Range("D1").Value = "Discrepancies"

wBook1.Worksheets("tempMissingRows").Range("E1").Value = "Found?"

With wBook1.Worksheets("tempMissingRows").Columns("A:E").Rows("1").Interior

.ColorIndex = 19

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

With wBook1.Worksheets("tempMissingRows").Columns("A:E").Rows("1")

.Font.Bold = True

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

End With

With wBook1.Worksheets("tempMissingRows").Columns("A:E").Rows("2:" & wb2endRow)

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlBottom

End With

With wBook1.Worksheets("tempMissingRows").Columns("A:E").Rows("1")

.Borders(xlEdgeLeft).LineStyle = xlContinuous

.Borders(xlEdgeLeft).Weight = xlThin

.Borders(xlEdgeLeft).ColorIndex = xlAutomatic

.Borders(xlEdgeTop).LineStyle = xlContinuous

.Borders(xlEdgeTop).Weight = xlThin

.Borders(xlEdgeTop).ColorIndex = xlAutomatic

.Borders(xlEdgeBottom).LineStyle = xlContinuous

.Borders(xlEdgeBottom).Weight = xlThin

.Borders(xlEdgeBottom).ColorIndex = xlAutomatic

.Borders(xlEdgeRight).LineStyle = xlContinuous

.Borders(xlEdgeRight).Weight = xlThin

.Borders(xlEdgeRight).ColorIndex = xlAutomatic

.Borders(xlInsideVertical).LineStyle = xlContinuous

.Borders(xlInsideVertical).Weight = xlThin

.Borders(xlInsideVertical).ColorIndex = xlAutomatic

End With

Columns("A:A").EntireColumn.AutoFit

Columns("B:B").ColumnWidth = 7.43

Columns("C:C").EntireColumn.AutoFit

Columns("D").EntireColumn.AutoFit

Columns("E:E").EntireColumn.AutoFit

wBook1.Worksheets("tempMissingRows").Range("A2").Value = 2

wBook1.Worksheets("tempMissingRows").Range("A3").Value = 3

wBook1.Worksheets("tempMissingRows").Range("A2:A3").Select

Selection.AutoFill Destination:=pRange, Type:=xlFillDefault

Range("A1").Select

With Worksheets("tempMissingRows")

For Each c In pRange

c.Offset(0, 1).Value = Application.Match(c.Value, (Worksheets("Data").Columns("A")), 0)

c.Offset(0, 2).Value = Application.Match(c.Value, (Worksheets("Adjustments").Columns("A")), 0)

c.Offset(0, 3).Value = Application.Match(c.Value, (Worksheets("Discrepancies").Columns("A")), 0)

c.Offset(0, 4).Value = WorksheetFunction.CountIf(Worksheets("tempMissingRows").Columns("B:E").Rows(c.Row), "<>#N/A")

Next c

End With

If WorksheetFunction.CountIf(Worksheets("tempMissingRows").Columns("E"), "1") = 0 Then

Application.DisplayAlerts = False

Worksheets("tempMissingRows").Delete

End If

Application.DisplayAlerts = True

Application.ScreenUpdating = True



End Sub