2

Let's say I have this.

Words             Mark                 Suffix
Happily                                ly
Emotional                              dom                              
Emotionally
Surfdom

I want to mark 1 if the word ends with some suffix in the suffix list, and 0 otherwise.

Words             Mark                 Suffix
Happily            1                   ly
Emotional          0                   dom                              
Emotionally        1
Surfdom            1

I tried lookup and vlookup but they are incomplete. How can I do this?

JvdV
  • 70,606
  • 8
  • 39
  • 70
user42459
  • 883
  • 3
  • 12
  • 29
  • 1
    If those three columns are A,B & C then B2 = `=IF(OR(RIGHT(A2,LEN($C$2))="ly",RIGHT(A2,LEN($C$3))="dom"),1,0)` – Naresh May 18 '20 at 03:09
  • @NareshBhople When you click to type a new comment, it says "Use comments to ask for more information or suggest improvements. Avoid answering questions in comments." That means: Please don't answer questions in comments. – teylyn May 18 '20 at 03:22
  • 1
    @teylyn . Thanks for the guidance. I am not sure if this is the answer, I added the comment just to find a response of the OP if this is what he/she is looking for. – Naresh May 18 '20 at 03:27
  • @NareshBhople Well, that's what answers are for, really. The OP can always comment on the answer. Answers can be upvoted and marked as solutions. I upvoted yours. – teylyn May 18 '20 at 03:39

4 Answers4

3

If those three columns are A,B & C then B2 = =IF(OR(RIGHT(A2,LEN($C$2))=$C$2,RIGHT(A2,LEN($C$3))=$C$3),1,0)

Above formula gives flexibility, to put any values in C2 and C3 and mark them.

You don't have to refer to column C =IF(OR(RIGHT(A2,2)="ly",RIGHT(A2,3)="dom"),1,0)

This is not suitable if you have a large data in column C. In that case, you may need a VBA solution.

Naresh
  • 2,984
  • 2
  • 9
  • 15
3

Here's an approach which shall help you extend what @NareshBhople has suggested here.

=IFERROR(LOOKUP(2,1/(RIGHT(A2,LEN($C$2:$C$3))=$C$2:$C$3)),0)

  1. Adjust the range in C column to suit criteria.

  2. Do not select whole column and

  3. Make sure that the criteria range is without blanks.

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
3

I believe you are looking for COUNTIF with OR construct in itself through wildcards:

=SUMPRODUCT(COUNTIF(A2,"*"&C$2:C$3))

Or if you have O365, =SUM() instead of =SUMPRODUCT(). If you don't need to refer to column C per se, we can create our own array:

=SUM(COUNTIF(A2,{"*ly","*dom"}))

This would always give you either 1 or 0 since a string can only have one ending =)

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Awesome! I like it as it doesn't need IFERROR masking the way I have done. Just one doubt, why not use `=SUM(COUNTIF(A2,"*"&C$2:C$3))` which is shorter? – shrivallabha.redij May 18 '20 at 08:22
  • I agree on CSE part but the `SUM+COUNTIF` formula works just fine without CSE. – shrivallabha.redij May 18 '20 at 09:02
  • 1
    Ah, yes that's exactly why it works the way it does for you (as you can see implicit intersection `@` is removed in front of `SUM` =) @shrivallabha.redij. So for O365 we can change `SUMPRODUCT` into `SUM`. Glad that mistery is solved. hehe – JvdV May 18 '20 at 10:04
  • 1
    Amazing. Thank you! – user42459 May 19 '20 at 03:16
2

I didn't find a worksheet function to do this job but here is a UDF (User-Defined Function) that works like one.

Function Suffixed(Cell As Range) As Integer

    Dim Suff        As Variant          ' list of Suffixes
    Dim Word        As String           ' the word extracted from 'Cell'
    Dim L           As Integer          ' length of suffix string
    Dim R           As Long             ' row counter

    Word = Cell.Value
    If Len(Word) Then                   ' skip if blank (return 0)
        ' set the range in column D, starting in row 2 to the column's end
        Suff = Range(Cells(2, "D"), Cells(Rows.Count, "D").End(xlUp)).Value
        For R = 1 To UBound(Suff)
            L = Len(Suff(R, 1))
            If StrComp(Right(Word, L), Suff(R, 1), vbTextCompare) = 0 Then
                Suffixed = 1
                Exit For
            End If
        Next R
    End If
End Function

Install the code in a standard code module. That is none of the code modules that Excel sets up. You must insert it and its default name will be Module1. After that you can call the function from the worksheet with the kind of syntax you are accustomed to. For example,

=Suffixed(A2)

The function will return 1 if the word ends on a suffix in your list, else zero. You may modify the code to move the list of suffixes to where you want it. Change two instances of the column letter and one instance of the number 2 which specifies the first row of your list. 

    ' set the range in column D, starting in row 2 to the column's end
    Suff = Range(Cells(2, "D"), Cells(Rows.Count, "D").End(xlUp)).Value

The end of the list is dynamic and the code will find it. Don't leave blanks in the list.

Variatus
  • 14,293
  • 2
  • 14
  • 30