CODEWITHSUNDEEP
excelformulacriteria
user3697526
user3697526

Reputation: 37

Excel - identify cells with multiple instances of the same values

Need to find which cells in column B that contain 3 or more of 21 specific values.

Here are the specific values (each 4 letter string is a single value.) - 3544 3538 3506 3502 3398 3396 3394 3392 3390 3388 3386 3384 3376 3362 3288 3270 3230 3228 1944 1866 1384

*To clarify more - There are 21 values (within a larger possible group of values) and need to identify the cells which contain 3 or more of those values.

*Edit 2 - In these single cells (which are all in column B) there are any number of values separated by a comma. The 21 possible values refers to specific values that need to be looked at within the larger set of values for 3 or more of the same occurrences. In other words, in these single cells, there will be numbers that are not any of the aforementioned 21 possible values.

enter image description here

The "Yes" or "No" would need to go in column C.

Upvotes: 0

Views: 1945

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

You can use an array formula.

With the values you are looking for in D1:D3, and your data in A1, you can use this:

EDIT added a preceding comma to make the formula more robust

=COUNT(FIND(","&$D$1:$D$3&",",","&A1&","))>=3

The formula should be entered by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula in the formula bar. Then fill down the formula as far as necessary

EDIT 2 If there might be spaces in the cell being tested, try this formula instead to remove those spaces:

=COUNT(FIND(","&$D$1:$D$3&",",","&SUBSTITUTE(A1," ","")&","))>=3

enter image description here

Upvotes: 1

nharrer
nharrer

Reputation: 636

EDIT: Here is a new version since you changed the requirements

You can use following Module Function: 

Public Function SplitFind(str As String, values As Range, minoccurs As Integer) As Boolean
    Let parts = Split(str, ",")
    Dim f1, f2, f3 As Boolean
    Dim occurs As Integer
    occurs = 0
    For Each cel In values.Cells
        Let sval = Val(cel.Value)
        Dim found As Boolean
        found = False
        For Each s In parts
            Let n = Val(s)
            If n = sval Then
                found = True
            End If
        Next s
        If found Then
            occurs = occurs + 1
        End If
    Next cel
    SplitFind = occurs >= minoccurs
End Function

Now let's say you have the 21 values you are looking for in cells A1 - U1. And the first cell with the comma separated values is in A2. Then you can use following formula to check if 3 or more of the values in A1-U1 appear in A2: 

=SplitFind(A2;$A$1:$U$1;3)

And here is a guide on how to add custom functions: http://www.dummies.com/software/microsoft-office/excel/how-to-create-custom-excel-functions/

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11988

Let's imagine all those special cells are in column A. For my answer, let's imagen your row 1 values 2212,2280,7809,8990,9113,3876 are in cell A1. I would use:

=IF(AND(IFERROR(FIND("8990";A1;1);0)>0;IFERROR(FIND("2212";A1;1);0)>0;IFERROR(FIND("7809";A1;1);0)>0);"YES";"NO")

I got spanish version of Excel, so i translated the formulas manually, but basically, is 1 IF combined with 1 AND, and 3 IFERROR and 3 FIND. The original formula is:

=SI(Y(SI.ERROR(ENCONTRAR("8990";A2;1);0)>0;SI.ERROR(ENCONTRAR("2212";A2;1);0)>0;SI.ERROR(ENCONTRAR("7809";A2;1);0)>0);"YES";"NO")

And if I record the macro, VBA does it like this (maybe can help you to type it):

ActiveCell.FormulaR1C1 = _
        "=IF(AND(IFERROR(FIND(""8990"",RC[-2],1),0)>0,IFERROR(FIND(""2212"",RC[-2],1),0)>0,IFERROR(FIND(""7809"",RC[-2],1),0)>0),""YES"",""NO"")"

Hope this helps. In my Excel it worked perfectly:

enter image description here

Upvotes: 0

Related Questions