Have Geek Brain

Excel / VBA to check if a row exists

I have a sheet full of some raw data, about 20,000 rows and 50 columns. (The number of lines should increase, perhaps double if not triple)

I need a formula to view this data and determine if there is a row for the data in the two specified columns. My current formula looks like this.

Function CheckExists(Table As Range, SearchCol1 As Integer, SearchVal1 As Variant, SearchCol2 As Integer, SearchVal2 As Variant)

    Dim i As Long
    Dim exists As Boolean

    exists = False

    For i = 1 To Table.Rows.Count
        If Table.Cells(i, SearchCol1) = SearchVal1 Then
            If Table.Cells(i, SearchCol2) = SearchVal2 Then
                exists = True
                Exit For
            End If
        End If
    Next i

    CheckExists = exists

End Function

I am running this formula from another sheet with about 5000 rows.

My problem is that this is killing my computer, it takes age to calculate the cells. I hope someone can offer some suggestions on how to do this faster or even better, a built-in formula that can do what I need.

+1


a source to share


6 answers


I suggest adding a column to concatenate the values ​​in the two columns of interest, and then use the MATCH worksheet function to find the new column.



+2


a source


You can add a column to the end of the raw data with a simple if formula, for example.

if(AND(A1=4, B1=6), 1, 0)

Where A1 / B1 are the columns to check and 4 and 6 will be replaced with actual values. Check if a row exists, just check the sum of that column:



if(sum(C:C) > 0, TRUE, FALSE)

where C is the formula column. In large Excel documents, I usually find this approach to be the most effective. If you post sample data, I can clarify further.

+3


a source


If you are using Cells.Find it will save you a lot of iterating over the cells that will never contain the value you want.

eg.

Cells.Find(What:="-1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
      xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
      , SearchFormat:=False).Activate

activates the cell in which it finds the value. Then you can check if the other cell you are comparing has the correct value (if the cell was positioned).

You may also need to check that the row / column you are in is the correct one for the value you are looking for, especially if you are looking for something like number 20 it will find number 20 in the wrong row.

Play it, I think you will find this a little faster than normal iteration.

Additional, possibly unfounded advice: with so much data, the database won't be useful?

Update:
I had a quick game - this function can replace the existing one you are using above, please let me know if it is faster.

Function FindCheckExists(Table As Range, SearchCol1 As Integer, _
                         SearchVal1 As Variant, SearchCol2 As Integer, _
                         SearchVal2 As Variant)

    Dim i As Long
    Dim exists As Boolean
    Dim result As Range

    exists = False
    Do While (Not exists)

        Set result = Cells.Find(What:=SearchVal1, After:=Cells(1, SearchCol1), _
          LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        If (result Is Nothing) Then Exit Function

        If result.Offset(0, (SearchCol2 - SearchCol1)).Value = SearchVal2 Then _
            exists = True

    Loop

    FindCheckExists = exists

End Function

The above has been reformatted with _ to fit it on the stackoverflow page, it might look like it in Excel without them.

+2


a source


You don't need to use VBA for this - you can do it with a regular front-end formula. The formula below (the first only rows 1-5000, but the second will do the entire column) will determine if a matching combination exists:

=SUMPRODUCT(--(A1:A5000="SearchValue1"),--(B1:B5000="SearchValue2"))

=SUMPRODUCT(--(A:A="SearchValue1"),--(B:B="SearchValue2"))

To use it, you just fix "SearchValue1" and "SearchValue2" as the values ​​you are looking for, and change the column letters if you are not using A and B.

+1


a source


I would say the same as rwmnau, except that I usually use:

=SUMPRODUCT((A1:A5000="SearchValue1")*(B1:B5000="SearchValue2"))

EDIT If you are using Excel 2007 you can use the COUNTIFS function (notice the S at the end).

+1


a source


I found it faster to use an autofilter. Filter it based on your criteria and then remove the remaining visible lines.

eg. Ws.Rows(1).Insert 'Filter does not check first row Ws.AutoFilterMode = False Ws.Cells.AutoFilter searchCol1, "=*" & searchVal1 & "*", , , False Ws.Cells.AutoFilter searchCol2, "=*" & searchVal2 & "*", , , False Ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).EntireRow.Delete ActiveSheet.AutoFilterMode = False

Hope it helps.

0


a source






More articles:


All Articles