Excel - 范围内的所有唯一单词

Question

也许我想做的太多了，但我有一列充满了文本。每个单元格都有任意数量的单词，例如:

     |         A          |
=====|====================|
  1  | apple pear yes cat |
  2  | apple cat dog      |
  3  | pear orange        |

我需要做的是创建一个列，它是该范围内所有唯一单词的列表。所以对于上面的例子，结果应该是:

     |         A          |   B    |
=====|====================|========|
  1  | apple pear yes cat | apple  |
  2  | apple cat dog      | pear   |
  3  | pear orange        | yes    |
  4  |                    | cat    |
  5  |                    | dog    |
  6  |                    | orange |

排名不分先后。有什么办法可以做到这一点吗？

Answer 1

此选项使用 1 个循环而不是 3 个循环，我喜欢使用字典或 Collection。

Sub Sample()
Dim varValues As Variant
Dim strAllValues As String
Dim i As Long
Dim d As Object

'Create empty Dictionary
Set d = CreateObject("Scripting.Dictionary")

'Create String With all possible Values
strAllValues = Join(Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp))), " ")

'Split All Values by space into array
varValues = Split(strAllValues, " ")

'Fill dictionary with all values (this filters out duplicates)
For i = LBound(varValues) To UBound(varValues)
    d(varValues(i)) = 1
Next i

'Write All The values back to your worksheet
Range("B1:B" & d.Count) = Application.Transpose(d.Keys)
End Sub