In excel cell there must be some string values with comma separated ex: apple,orange,grapes,pine app-CodePudding

Example:

if I have in A1: apple,Orange, Grapes I would like to have missing names in B1:Pineapple,Maa.

If A2: Grapes,Pineapple then I would have in B2:Apple,Orange,Maa

I searched for a solution online, but I find same type for missing numbers only. Please help on this

I'd be glad if I can have a solution here. Thanks.

I tired the below:

Public Function MissingWords As String
    Dim temp As String
    Temp = Replace (stringList, "") 
    temp = Replace(temp, "") 

    Dim arr As Variant
    Arr = Split (temp, ", ") 

    Dim newstrings As String
    Newstrings = " Apple,Orange,grapes,pineapple, maa"
          
    Dim i As Long
    For i = LBound (arr) To UBound(arr) 
        Newstrings = Replace (newstrings, arr(I) & ", ", "") 
    Next
    Newstrings = Left$(newstrings, Lena(newstrings) - 1) 
    Missingstrings = newstrings
End function

If I applied this in excel getting value error

CodePudding user response：

Use the following function in a worksheet like

=GetMissingWordsFromList(A1,"Apple,Orange,Grapes,Pineapple,Maa")

And if A1 is Pineapple,Maa you will get Apple,Orange,Grapes in return.

Note that this is case sensitive so Pineapple and pineapple is not considered the same. Also there must not be any spaces after your commas , because it cannot handle that.

Option Explicit

Public Function GetMissingWordsFromList(ByVal Words As String, ByVal WordsList As String, Optional ByVal Delimiter As String = ",") As String
    Dim WordsArr() As String
    WordsArr = Split(Words, Delimiter)
    
    Dim WordsListArr() As String
    WordsListArr = Split(WordsList, Delimiter)
    
    Dim RetVal As String
    
    Dim Word As Variant
    For Each Word In WordsListArr
        If Not IsInArray(Word, WordsArr) Then
            RetVal = RetVal & IIf(RetVal = vbNullString, "", ",") & Word
        End If
    Next Word
    
    GetMissingWordsFromList = RetVal
End Function

Private Function IsInArray(ByVal What As String, ByVal InArray As Variant) As Boolean
    IsInArray = IsNumeric(Application.Match(What, InArray, 0))
End Function

to make it case insensitive (so it will find Pineapple,Maa in a list like apple,orange,grapes,pineapple,maa) you need to use

WordsArr = Split(LCase(Words), Delimiter)

and

If Not IsInArray(LCase(Word), WordsArr) Then

instead.

CodePudding user response：

the last line for your function should be

MissingWords=NewStrings

Now, your function always returns nothing. Missingstrings is not defined in the function.