Instr to look for a complete, exact match of a string

Convert in to case insensitive

If InStr(1, tempList, cell.Value, 1) = 0 Then

Should be

    If InStr(1, cell.Value, tempList, 1) = 0 Then

Done. But it still does not add "Microprocessor" if "Advanced Microprocessor" is already in the list.

Also, I typed "microprocessor" above mistakenly (now corrected), there is no difference in case in the list.

Yes, it would take in duplicates.

I added a messagebox to show me the values instr is comparing (See below) and it looks like you are correct. However, the items are still not appearing in the list (So there is "Advanced Microprocessor", but not "Microprocessor" and "Logic Circuit", but not "Circuit").

It must be a problem with how the data is being written to the string list.

Dim tempList As Variant: tempList = ""
    For Each cell In categories
        If cell.Value <> "" And cell.Offset(0, -1).Value = Sheets("DropDowns").Range("a1") Then
            MsgBox (cell.Value & " offset: " & cell.Offset(0, -1).Value)
            If InStr(1, tempList, cell.Value, 1) = 0 Then
                If tempList = "" Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value))
            End If
        End If
    Next cell

Well, that's not the case either. Let me share the real values being entered.

Running through the code below, the first item is "Construction Materials", which gets added to the list. The second item that meets the criteria is "Advanced Electronic Construction Materials", which also gets added to the list. The third item is "Electronic Construction Materials", which does NOT get added to the list. The fourth item is "Tools", which does get added, and so on.

Not sure why it skips "Electronic Construction Materials". Any ideas?

Dim tempList As Variant: tempList = ""
    For Each cell In categories
        If cell.Value <> "" And cell.Offset(0, -1).Value = Sheets("DropDowns").Range("a1") Then
            MsgBox (cell.Value & vbNewLine & "Offset: " & cell.Offset(0, -1).Value)
            If InStr(1, tempList, cell.Value, 1) = 0 Then
                MsgBox ("Item to Add : " & cell.Value & vbNewLine & "Current List : " & tempList)
                If tempList = "" Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value))
                MsgBox ("Current List : " & tempList)
            End If
        End If
    Next cell

Running through the code below, the first item is "Construction Materials", which gets added to the list. The second item that meets the criteria is "Advanced Electronic Construction Materials", which also gets added to the list. The third item is "Electronic Construction Materials", which does NOT get added to the list. The fourth item is "Tools", which does get added, and so on.

Not sure why it skips "Electronic Construction Materials". Any ideas?

Yes, on the second test "Advanced Electronic Construction Materials" is not in the string, but on the third test "Electronic Construction Materials" is in the string (as part of "Advanced Electronic Construction Materials" )

Regards,

Tom

I have added a | at the beginnng of the list, but still not getting the needed results.

Regarding the question above, on the second test "Construction Materials" is also already in the list, but "Advanced Electronic Construction Materials" is added. Why will it not then add "Electronic Construction Matierials" as well, since both contain elements of an earlier entry?

Starting to tear hair out ha-ha

If you could put the delimiter "|" on either side of the string search then you would be able to get exact matches. You would have to modify the code so your string looked like:

"|Advanced Microprocessor|Supercomputer|Computer|Keyboard|Logic Circuit|"

When the string is finished you could remove the leading and trailing delimiters with the mid function, i.e.

tempList = Mid(tempList , 2, Len(tempList) - 2)

Regards,

Tom

I left out "NOT"

Yes, it NOT would take in duplicates.

Without code or a workbook to look at I'm not sure what you did. And I can't think of another way to explain what is happening but it is perfectly logical to me. Regardless this should meet your requirement (untested due to no workbook).

Dim tempList As Variant: tempList = ""
    For Each cell In categories
        If cell.Value <> "" And cell.Offset(0, -1).Value = Sheets(current).Range("a1") Then
            If InStr(tempList, "|" & cell.Value & "|") = 0 Then
                If tempList = "" Then tempList = "|" & Trim(CStr(cell.Value)) & "|" Else tempList = tempList & Trim(CStr(cell.Value)) & "|"
            End If
        End If
    Next cell
    tempList = Mid(tempList, 2, Len(tempList) - 2)

Regards,

Tom

Worked like a charm!! Thanks again.