Search code examples
excelformulacriteria

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.

Solution

  • 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