![]() ![]() ![]() You can combine this UDF inside a SUB to apply for a full range. If Len(GET_DUP_WORDS) > 0 Then GET_DUP_WORDS = Left(GET_DUP_WORDS, Len(GET_DUP_WORDS) - 2)įormula in column C is: =GET_DUP_WORDS(A1 B1)įormula in column D is the opposite: =GET_DUP_WORDS(B1 A1) 'get rid of last comma in case there is something If test > 0 Then GET_DUP_WORDS = GET_DUP_WORDS & targetWord & ", " Test = Evaluate(Replace("=SUM(COUNTIF(" & rng2.Address & ",))", "targetWord", targetWord)) 'or at start of string with a trailing blank 'or at the end of string with a leading blank Notice the output sorting is different depending on how you set up rng1 and rng2 but the words are the same: Public Function GET_DUP_WORDS(ByVal rng1 As Range, ByVal rng2 As Range) As Stringįor i = LBound(wordArray) To UBound(wordArray) This UDF needs 2 arguments: rng1 would be words to check and rng2 would be the range containing the duplicates of rng1. ![]() If the sum is greater than 0,it's a dupplicate. ' Exit For ' Exit the loop once a repeated word is foundĬreate and UDF in VBA to check this, considering a word anything between 2 blanks, or at start of string with trailink blank or end of string with leading blank. Is there an antonym for 'dim'/ synonym for 'lighten' Ask Question Asked 7 years, 4 months ago. Set dict = CreateObject("scripting.dictionary")Ĭells(i, 3).Value = Cells(i, 3).Value & " " & word Im looking for a word that refers to when a dark room is slightly lightened- for example, by a candle or a weak lightbulb, but not so much that I could use 'brightened' to describe the action. You should be using a dictionary, collection or array to record what you have found and processed for subsequent disposal, rather than leaving the loop immediately. I can get one value Grand how get both repeated words( Grand,Theft) values in col b? ' If the word occurs more than once, paste it in column C for the current rowĮxit For ' Exit the loop once a repeated word is found WordCount = UBound(Split(cellValueA, word)) - LBound(Split(cellValueA, word)) ' Count the number of occurrences of the word in the string In tissues and three-dimensional (3-D) matrix, the apicomplexan parasite Toxoplasma gondii moves along a helical path. To infect, the parasite needs to travel through different types of tissues and invade into various types of cells. DIM Synonyms: 122 Synonyms & Antonyms for DIM ( of 13) Find 122 ways to say DIM, along with antonyms, related words, and example sentences at, the worlds most trusted free thesaurus. 'Cells(i, 3).Value = Cells(i, 3).Value & " " & word Apicomplexans parasitize a wide range of hosts. If InStr(cellValueB, wordsA(wordIndex)) = 0 ThenĬells(i, 1).Characters(InStr(cellValueA, word), Len(word)).Font.ColorIndex = 3 ' Highlight in red ' If the word is not found in column B, highlight it in column A ' Get the values in columns A and B for the current row ' Loop through each row of data in column A and compare with column B LastRow = Cells(Rows.Count, 1).End(xlUp).Row ![]()
0 Comments
Leave a Reply. |