Excel - 范围内的所有唯一字词 [英] Excel - All unique words in a range

问题描述

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

Perhaps I'm trying to do too much, but I have a column filled with text. Each cell has an arbitrary number of words, so for example:

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

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

What I need to do is create a column which is a list of all unique words in that range. So for the above example, the result should be:

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

没有特定的顺序。有没有办法这样做？

In no particular order. Is there any way to do this?

推荐答案

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

This option uses 1 loop instead of 3, I like to use a dictionary instead or 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

这篇关于Excel - 范围内的所有唯一字词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！