CODEWITHSUNDEEP
excelvbaexcel-formula
user8517443
user8517443

Reputation:

Excel Equation to determine if every value in one range has a match with a value in another range

As a completeness check for the user of a financial model, I need an equation that will tell if every product heirarchy has been accounted for. This means every value in range B1:B100 on Tab1 should be accounted for in range C2:c200 on Tab2. If this condition is met, the formula should say "Complete", if not "Incomplete". Anyone aware of how to create a function like this?

=if(Every value in tab1!b1:b100 has a match in tab2!c2:c200, "Complete", "Incomplete")

Upvotes: 1

Views: 75

Answers (2)

DisplayName
DisplayName

Reputation: 13386

just to add a VBA solution

Sub columnsMatch()
    Dim filters As Variant
    filters = Application.Transpose(Worksheets("Sheet2").Range("C2:C200").Value)

    With Worksheets("Sheet1").Range("b1:b100") 'reference "Sheet1" sheet range B1:B100
        .AutoFilter Field:=1, Criteria1:=filters, Operator:=xlFilterValues ' filter referenced range with values from "Sheet2" sheet range C2:C200
        .Parent.Range("M1").Value = IIf(Application.WorksheetFunction.Subtotal(103, .Cells) = .Count And Not IsError(Application.Match(.Cells(1, 1).Value, filters, 0)), "Complete", "Incomplete")
        .Parent.AutoFilterMode = False ' remove filters
    End With
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Well to count the number of matches, we can use:

=SUMPRODUCT(COUNTIF(Sheet1!B1:B100,Sheet2!C2:C200))

To test if everything in the first sheet can be found in the second sheet:

=SUMPRODUCT(COUNTIF(Sheet1!B1:B100,Sheet2!C2:C200)) = COUNTA(Sheet1!B1:B100)

or

=SUMPRODUCT(COUNTIF(Sheet1!B1:B100,Sheet2!C2:C200)) = 100

if there are no blanks or dups.

Upvotes: 4

Related Questions