从excel中的单元格中删除重复项

So I've got a list of strings, for which some of the strings contain duplicates.

所以我有一个字符串列表，其中一些字符串包含重复项。

For example:

例如：

13-Hexadecenoic acid;13-methyl-4-pentadecenoic acid;14-Methyl-6-pentadecenoic acid;15-Hexadecanolide;3-Hexadecenoic acid;4-hexadecenoic acid;13-Hexadecenoic acid;13-methyl-4-pentadecenoic acid;14-Methyl-6-pentadecenoic acid;15-Hexadecanolide;3-Hexadecenoic acid;4-hexadecenoic acid;

13-十六碳烯酸; 13-甲基-4-十五碳烯酸; 14-甲基-6-十五碳烯酸; 15-十六内酯; 3-十六碳烯酸; 4-十六碳烯酸; 13-十六碳烯酸; 13-甲基-4-十五碳烯酸; 14-甲基-6-十五碳烯酸; 15-十六内酯; 3-十六碳烯酸; 4-十六碳烯酸;

So I came across a macro online and tweaked it to work for my problem, which goes like this:

所以我在网上看到了一个宏并调整它以解决我的问题，如下所示：

Function stringOfUniques(inputString As String) As String
    Dim inArray() As String
    Dim xVal As Variant
    inArray = Split(inputString, ";")
    For Each xVal In inArray
        If InStr(stringOfUniques, Trim(xVal)) = 0 Then _
        stringOfUniques = stringOfUniques & Trim(xVal) & ","
    Next xVal

End Function

And for some of my strings this works brilliantly, however for the example above it will return the string with the duplicates removed, but weirdly will get rid off both copies of one of the words '3-Hexadecenoic acid'. So basically

而对于我的一些字符串，它的工作非常出色，但是对于上面的示例，它将返回删除了重复项的字符串，但奇怪的是将删除其中一个单词“3-Hexadecenoic acid”的两个副本。所以基本上

What I should get:

我应该得到什么：

13-Hexadecenoic acid;13-methyl-4-pentadecenoic acid;14-Methyl-6-pentadecenoic acid;15-Hexadecanolide;3-Hexadecenoic acid;4-hexadecenoic acid;

13-十六碳烯酸; 13-甲基-4-十五碳烯酸; 14-甲基-6-十五碳烯酸; 15-十六内酯; 3-十六碳烯酸; 4-十六碳烯酸;

What I actually get:

我真正得到的是：

13-Hexadecenoic acid;13-methyl-4-pentadecenoic acid;14-Methyl-6-pentadecenoic acid;15-Hexadecanolide;4-hexadecenoic acid;

13-十六碳烯酸; 13-甲基-4-十五碳烯酸; 14-甲基-6-十五碳烯酸; 15-十六内酯; 4-十六碳烯酸;

Is there something in my code that's causing this to happen?

我的代码中有什么东西会导致这种情况发生吗？

1 个解决方案

#1

Probably there are several ways to do this, but Dictionary objects are great for enforcing uniqueness.

可能有几种方法可以做到这一点，但是Dictionary对象非常适合强制执行唯一性。

Function stringOfUniques(inputString As String, delimiter as String)
Dim xVal As Variant
Dim dict as Object
Set dict = CreateObject("Scripting.Dictionary")

For Each xVal In Split(inputString, delimiter)
    dict(xVal) = xVal
Next xVal

stringOfUniques = Join(dict.Keys(),",")
End Function

This function has also been modified to accept a variable delimiter argument, so you will pass the input string and the delimiter: ";" to the function, which will return a comma-separated string.

此函数也已被修改为接受变量分隔符参数，因此您将传递输入字符串和分隔符：“;”到函数，它将返回逗号分隔的字符串。

A Note on Dictionaries:

关于词典的注释：

Dictionaries store key/value pairs. The keys must be unique.

字典存储键/值对。钥匙必须是唯一的。

In the example, I use a simple assignment to the dictionary object: dict(key) = key. A dictionary will automatically add or overwrite an item when referenced by it's key, so this is a really simple way of enforcing uniqueness.

在这个例子中，我使用了对字典对象的简单赋值：dict（key）= key。当字典引用时，字典会自动添加或覆盖项目，因此这是一种强制执行唯一性的简单方法。

In other circumstances (i.e., you want to count the number of occurrences of each key), you would want to test using the dict.Exists(key) in order to modify the value without overwriting the key/value pair, for example:

在其他情况下（即，您想要计算每个键的出现次数），您可能希望使用dict.Exists（key）进行测试，以便在不覆盖键/值对的情况下修改该值，例如：

'Assigns a "count" value to the dictionary for each unique Key
For Each xVal In Split(inputString, delimiter)
    If dict.Exists(xVal) Then
        dict(xVal) = dict(xVal) + 1
    Else
        dict(xVal) = 1
    End If
Next xVal

'Read the "count" from each key:
For Each xVal in dict.Keys()
    MsgBox xVal & " appears " & dict(xVal) & " times"
Next

#1