CODEWITHSUNDEEP
vbaexcel
Dorota Babś
Dorota Babś

Reputation: 23

Return something when value is at least found once in column B for item in column A

I'm looking for ab Excel formula solving the below:

I have list of account numbers in column A and in column B a list of charge methods associated to each account. One account can have a few charge methods associated - for example:

Account no  Charge method
A1  IC1
A1  IC2
A1  Exempt
A2  IC1
A2  IC2
A3  IC1
A3  IC2
A3  IC3
A3  Exempt
A4  IC1
A4  IC2
A4  Exampt

I would like to specify which account have at least one charge method "Exempt". So for example: in column C value "Exempt" if if for A1 account "Exempt" is mentioned at least once in column B. I tried to use vlookup TRUE, but I'm not sure if it will be always accurate.

Upvotes: 1

Views: 142

Answers (4)

Dorota Babś
Dorota Babś

Reputation: 23

We came to a conclusion that the most simple formula for the given example would be this one: =IF(COUNTIFS(A:A,A2,B:B,"Exempt"),"Exempt","")

Thank you all for your comments!

Upvotes: 1

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

This simple formula works without any helper columns or VBA:

=IF(SUM(--($A$2:$A$13=A2)*($B$2:$B$13="Exempt"))>0,"Exempt","")

entered as an array formula using Ctrl Shift Enter, and then copied down.

enter image description here

Notes:

  • $A$2:$A$13=A2 returns an array containing {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
  • Same idea for $B$2:$B$13="Exempt": it returns {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
  • -- converts boolean TRUE/FALSE to 1/0.
  • Multiplying these 1's and 0's using * is equivalent to a logical AND on the corresponding boolean values; the result is {0;0;1;0;0;0;0;0;0;0;0;0}
  • If the sum of that array is greater than zero, then you've got a match.

Upvotes: 2

99moorem
99moorem

Reputation: 1983

You could try this vba solution, it expects accounts/charge methods to be in columns A/B and it will populate into column D

Sub t()

'change activesheet to sheets("NAME OF SHEET") if needed
With ActiveSheet

    'If list is not empty, clear it
    If .Range("D2").Value <> "" Then
        .Range("D2:" & .Range("D1").End(xlDown).Address).ClearContents
    End If

    For Each cell In .Range("A2:" & .Range("A2").End(xlDown).Address)

        'if exempt found
        If cell.Offset(0, 1).Value = "Exempt" Then

            'If list is empty, start list
            If .Range("D2").Value = "" Then
                .Range("D2").Value = cell.Value
            Else
                'is account already in list
                Found = 0
                For Each c In .Range("D1:" & .Range("D1").End(xlDown).Address)
                    If c.Value = cell.Value Then
                        Found = 1
                    End If
                Next

                'If not in list, add it
                If Found = 0 Then .Range("D1").End(xlDown).Offset(1, 0).Value = cell.Value
            End If
        End If
    Next
End With

End Sub

Upvotes: 0

Mark Butler
Mark Butler

Reputation: 895

The setup which follows will output this:

Account no  Charge method  
A1          IC1            Exempt
A1          IC2            Exempt
A1          Exempt         Exempt
A2          IC1            
A2          IC2            
A3          IC1            Exempt
A3          IC2            Exempt
A3          IC3            Exempt
A3          Exempt         Exempt
A4          IC1            Exempt
A4          IC2            Exempt
A4          Exempt         Exempt

I have set the third column as "Exempt" if there exists a row with the corresponding Account No. in A, and "Exempt" in column B; and blank if not.

If I have misunderstood the problem, please correct me.

Create a Column C with the following formula:

=A1&" "&B1

Drag this down for the whole column as far as necessary.

Hide Column C. Then create Column D with

=IF(COUNTIF(C:C,A1& " Exempt")>0,"Exempt","")

Upvotes: 0

Related Questions