In Excel 2016, I have a column with cells that contain comma delimited strings. I am trying to count the number of unique strings in the entire column.
I know how to count the number of unique comma delimited strings within one cell and I know that I could collapse the data in my column into a single cell and do it that way. The problem is that would exceed the character limit of one cell since I have too much data.
Here is an example image which shows what I am trying to do:
The number 8 in B3 is the final result I am looking to get.
I don't know if this is possible to do using an array formula but I have attempted to write down the logic of a vba function to accomplish this. What I have so far is this pseudocode:
Function UniqueK (cells As Range) As Integer
Dim count As Integer
Dim temp_array() As String
Dim final_array() As String
Dim cell As Variant
For cell In cells
temp_array = split(cell,",")
append temp_array to final_array
Next cell
remove duplicates in final_array
count = number of entries in final_array
UniqueK = count
End function
This is probably a really slow way of doing it and I was wondering if there was an easier way of accomplishing this task or if there is an existing way of doing it that I could not find. If not, are there ways to remove duplicates in a string array and ways to append a string array to the end of another string array so I can get this function to work?
