In cell C1 of Excel, how can you select coma separated cells/values from column A that are not found in a coma separated list of values from cell B1?

Question

In cell C1 of Excel, how can you use a formula to select a coma separated string of all missing cells from all rows in column A that the values are not found in a coma separated list of values from cell B1?

Very barebones question. Something like [this](https://stackoverflow.com/q/60575515/9758194) maybe? — JvdV, Jan 01 '21 at 11:48

score 1 · Answer 1 · answered Jan 01 '21 at 13:15

1

A formula solution

In C1, enter array formula (Ctrl+Shift+Enter):

=TEXTJOIN(",",1,IF(ISERR(SEARCH(A1:A6,B1)),A1:A6,""))

answered Jan 01 '21 at 13:15

bosco_yip

3,762
2
5
10

..........Beautiful formula ! ........................ – Gary's Student Jan 01 '21 at 13:25

score 0 · Answer 2 · answered Jan 01 '21 at 12:24

Try the following User Defined Function:

Option Explicit

Public Function WhatsMissing(rng As Range, css As String) As String
    Dim rngT As Range, ws As Worksheet, cssT As String
    Dim r As Range, v As String, vT As String
    
    Set ws = rng.Parent
    Set rngT = Intersect(rng, ws.UsedRange)
    
    cssT = "," & css & ","
    WhatsMissing = ""
    
    For Each r In rngT
        v = r.Value
        If v <> "" Then
            vT = "," & v & ","
            If InStr(cssT, vT) = 0 Then
                WhatsMissing = WhatsMissing & v & ","
            End If
        End If
    Next r
    
    WhatsMissing = Mid(WhatsMissing, 1, Len(WhatsMissing) - 1)
End Function

An example:

In cell C1 of Excel, how can you select coma separated cells/values from column A that are not found in a coma separated list of values from cell B1?

2 Answers2