CODEWITHSUNDEEP
excelvba
simon_innes95
simon_innes95

Reputation: 3

How to create a nested loop to check if a value exists in a second list

I am trying to compare values in two lists. I want my code to compare a value in the first list and check all the entries in the second list. If there is a match then the code will print true next to the value in the first list and if not it will print false.

The problem I am having is that my code only compares values that are in the same row.

The code runs and I have tried it on a two smaller lists to make sure the data types are to same and there aren't any extra spaces or commas in the lists that would lead to a "False" output. I have also tried changing the order of the for and if statements but this doesn't work either.

Sub findvalues()
    For i = 2 To 16
        For j = 2 To 16
            If Cells(i, 3).Value = Cells(i, 1).Value Then
                Cells(i, 4).Value = "TRUE"
            ElseIf Cells(i, 3).Value = Cells(j + 1, 1).Value Then
                Cells(i, 4).Value = "TRUE"
            Else
                Cells(i, 4).Value = "FALSE"
            End If
        Next j
    Next i
End Sub

Here are the two lists I am testing the code on

Upvotes: 0

Views: 516

Answers (4)

Anthony
Anthony

Reputation: 552

VBA code to reconcile two lists.

 Sub Reconciliation()
Dim endRow As Long
Dim ICount As Long
Dim Match1() As Variant
Dim Match2() As Variant
Dim ws As Worksheet
Set ws = Worksheets("Recon")
ICount = 0
endRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
endRow1 = ws.Cells(ws.Rows.Count, 11).End(xlUp).Row
Match1 = Sheet1.Range("b2:b" & endRow)
Match2 = Sheet1.Range("K2:K" & endRow1)

For i = LBound(Match1) To UBound(Match1)
For j = LBound(Match2) To UBound(Match2)
    If Match1(i, 1) = Match2(j, 1) Then
    ICount = ICount + 1
        Sheet1.Range("C" & i + 1).Value = ICount
        Sheet1.Range("L" & j + 1).Value = ICount
    Else

   End If
   Next j
Next i
End Sub

Upvotes: 0

Error 1004
Error 1004

Reputation: 8220

There are many was to achieve that. one of them is by using IF & COUNTIF

Formula

=IF(COUNTIF($E$2:$E$6,A2)>0,"TRUE","FALSE")

Results:

enter image description here

VBA CODE

Option Explicit

Sub findvalues()

    Dim i As Long
    Dim rng As Range

    With ThisWorkbook.Worksheets("Sheet1") 'Change if needed

        Set rng = .Range("A2:A130") 'set rng to includes values from column A, rows 2:130

        For i = 2 To 130 'Loop from row 2 to 130
            'Check if the values in column C includes in the rng
            If Application.WorksheetFunction.CountIf(rng, .Range("C" & i).Value) > 0 Then
                .Range("D" & i).Value = "TRUE"
            Else
                .Range("D" & i).Value = "FALSE"
            End If

        Next i

    End With

End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

I don't see any need for VBA - formulas are the way to go - but to avoid two loops one could do this:

Sub findvalues()

Dim i As Long

For i = 2 To 130
    Cells(i, 4).Value = IsNumeric(Application.Match(Cells(i, 1).Value, Range("C2:C130"), 0))
Next i

End Sub

Update: this does not cater for multiple matches.

Upvotes: 2

Skin
Skin

Reputation: 11197

Slight mods to your code based on the data you provided in columns 1 & 3. As always, things could be improved but this should get you going ...

Sub findvalues()
    Dim i As Long, j As Long, bResult As Boolean

    For i = 2 To 16
        strValueToLookFor = Cells(i, 1)

        For j = 2 To 16
            bResult = False

            If strValueToLookFor = Cells(j, 3).Value Then
                bResult = True
                Exit For
            End If
        Next j

        Cells(i, 6).Value = bResult
    Next i
End Sub

... you may just need to flick the columns over so the first list searches on the second list or vice versa.

Upvotes: 2

Related Questions