有办法找到重复的单词吗？ [英] Is there way to find duplicate words?

问题描述

所以我试图找到/制作一个可以在excel中找到我所有重复的单词的程序。例如在A2人中的A1某人等等，但是我会有某人的倍数或另一个字，我需要把这些信息集中在一起，但是我需要这样做，那么有没有办法找到重复的单词并连接起来？

我也一直在寻找使用FIND来查看对于他们来说，它也没有运气，我也一直在使用过滤器，但是我不知道一个方法来压缩重复的操作，而不是手动执行，我也想知道你可以在哪里找到查找，替换和等等？如果我可以发现我可以改变REMOVE DUPLICATES的代码来改变它的话，但是我真的不知道这是否真的可以工作，有什么会有帮助的。 / p>

例如：

  culomn1  -  culomn2 ----- culomn3 
y ------ A -----（nothing）
z ------ B -----（nothing）
z ------（nothing ）----- I 
x ------（nothing）----- k 
y ------（nothing）----- j 
x  - ----- C ------（没有）
  

到这个

  culomn1  -  culomn2 ----- culomn3 
 y ------ A ------ j 
 z ------ B ------ I 
 x ------ C ------ k 
  

除了字母是单词。

解决方案

这将给你一个函数，可以根据特定的字符串找到第一个非空白单元格

  Option Explicit 
 
函数NonBlankLookup（SearchTxt As String，LookIn As Range，OffSetRows As Long）As Variant 
 Dim loc As Range 
 Dim FirstFound As Range 
 
设置loc = LookIn.Find（what：= SearchTxt）
 While not（loc is Nothing）
如果不是IsEmpty（loc.Offset（0，OffSetRows））然后
 NonBlankLookup = loc.Offset（0，OffSetRows）.Value 
退出函数
结束如果
如果FirstFound是Nothing然后
设置FirstFound = loc 
 ElseIf loc = FirstFound然后
 NonBlankLookup = CVErr（2000）
退出函数
结束如果
设置loc = LookIn.Find（what：= SearchTxt，after：= loc）
 Wend 
 NonBlankLookup = CVErr（2000）
结束函数
  

使用，将此代码插入到一个模块中，然后在您的excel电子表格中，您可以使用如 = NonBlankLookup（E1，$ A $ 1：$ A $ 6,1）将在 A1：A6 中搜索您的文本，并向右检查1列。如果没有找到匹配搜索字符串的文本，或者如果找到文本，但没有指定列中的数据，则返回 #NULL！。

这也有一个对于vlookup来说，稍微有点好处，因为它会允许负偏移，所以你可以在第2列中找到搜索文本，通过使用-1作为偏移量，你可以从列1返回数据

只是你知道，因为 .find 的工作方式，当你指定一个范围，它将从第二个单元格，然后下来，搜索您最后给出的第一个单元格。

eg我的例子为： A1：A6 ，它将搜索A2，A3，A4，A5，A6 ，最后 A1

So I'm trying to find/make a program that will find all my duplicate words in excel. For example in A1 "someone" in A2 ""person" and ect but I'll have "someone" multiples times or another word and I need to condense that information together. But I need to do it in a way where I don't search manually to concatenate duplicates. So is there a way to find the duplicate words and concatenate them?

I have also been looking into doing it using "FIND" to look for them but it has yielded no luck yet. I also have been using the "FILTER" but I don't know a way to condense the duplicates without doing it manually. I also been wondering where you can find the code for functions like "FIND, REPLACE and ect."? If I could find that I could change the coding for "REMOVE DUPLICATES" to change it for words. But hey I don't really know if that really would work or not. Anything would help.

For example:

culomn1 - culomn2 -----culomn3
y ------ A -----(nothing) 
z ------ B -----(nothing) 
z ------ (nothing)-----I
x ------ (nothing)-----k
y ------ (nothing)-----j 
x ------ C ------(nothing)

to this

culomn1 - culomn2 -----culomn3
y------A------j
z------B------I
x------C------k

except the letters are words.

解决方案

This will give you a function that will find the first non-blank cell against a specific string

Option Explicit

Function NonBlankLookup(SearchTxt As String, LookIn As Range, OffSetRows As Long) As Variant
Dim loc As Range
Dim FirstFound As Range

Set loc = LookIn.Find(what:=SearchTxt)
While Not (loc Is Nothing)
    If Not IsEmpty(loc.Offset(0, OffSetRows)) Then
        NonBlankLookup = loc.Offset(0, OffSetRows).Value
        Exit Function
    End If
    If FirstFound Is Nothing Then
        Set FirstFound = loc
    ElseIf loc = FirstFound Then
        NonBlankLookup = CVErr(2000)
        Exit Function
    End If
    Set loc = LookIn.Find(what:=SearchTxt, after:=loc)
Wend
NonBlankLookup = CVErr(2000)
End Function

to use, insert this code into a module, then in your excel spreadsheet, you can use a formula like =NonBlankLookup(E1,$A$1:$A$6,1) which will search for your text in A1:A6, and check 1 column to the right. If no text is found that matches the search string, or if the text is found but no data exists in the specified column, #NULL! is returned.
This also has a slight advantage to vlookup, as it will allow negative offset, so you could have the search text in column 2, and by using -1 for the offset, you could return data from column 1

Just so you are aware, because of the way that .find works, when you specify a range, it will start at the 2nd cell, and go down, and search the first cell you give it last.
e.g. with my example of A1:A6, it will search A2,A3,A4,A5,A6 and finally A1

这篇关于有办法找到重复的单词吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！