Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 22nd 20, 10:03 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2018
Posts: 23
Default Eliminate duplicate words from string

If I have a string of words, such as:

sWords = "person person woman man camera TV TV person man man"

What is an efficient way to remove duplicate words? The variable sWords should contain "person woman man camera TV".

Thanks!

  #2   Report Post  
Old November 22nd 20, 10:25 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default Eliminate duplicate words from string

Hi,

Am Sun, 22 Nov 2020 01:03:30 -0800 (PST) schrieb Tatsujin:

If I have a string of words, such as:

sWords = "person person woman man camera TV TV person man man"

What is an efficient way to remove duplicate words? The variable sWords should contain "person woman man camera TV".


try:

Sub Test()
Dim sWords As String
Dim myDic As Object
Dim varWords As Variant, varTmp As Variant
Dim i As Integer

sWords = "person person woman man camera TV TV person man man"
varWords = Split(sWords, " ")
Set myDic = CreateObject("scripting.dictionary")
For i = LBound(varWords) To UBound(varWords)
myDic(varWords(i)) = varWords(i)
Next i
varTmp = myDic.items
sWords = Join(varTmp, " ")
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old November 30th 20, 08:16 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2018
Posts: 23
Default Eliminate duplicate words from string

Thanks Claus, that works perfectly...

BTW, I just now realized that my string input will sometimes include punctuation marks at the end of words. The punctuation marks that I am most concerned about are periods, commas, and semi-colons. For example:

sWords = "person woman and man. TV, man, woman; but no person."

What's a good way to remove punctuation marks? That way, my strings only contain words without the punctuation marks to the right of words.

Thanks!
  #4   Report Post  
Old November 30th 20, 03:08 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default Eliminate duplicate words from string

Hi,

Am Sun, 29 Nov 2020 23:16:59 -0800 (PST) schrieb Tatsujin:

BTW, I just now realized that my string input will sometimes include punctuation marks at the end of words. The punctuation marks that I am most concerned about are periods, commas, and semi-colons. For example:

sWords = "person woman and man. TV, man, woman; but no person."

What's a good way to remove punctuation marks? That way, my strings only contain words without the punctuation marks to the right of words.


store the unique words without punctuation marks in sWords:

Sub Test()
Dim myDic As Object, re As Object
Dim sWords As String
Dim varText() As Variant, varOut As Variant
Dim i As Long
Dim ptrn, Match, Matches

Set myDic = CreateObject("Scripting.Dictionary")
Set re = CreateObject("vbscript.regexp")

sWords = "person woman and man. TV, man, woman; but no person."

'Separate all "words"
ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(sWords)

ReDim Preserve varText(Matches.Count - 1)
For Each Match In Matches
varText(i) = Match.Value
i = i + 1
Next

'Create unique words
For i = LBound(varText) To UBound(varText)
myDic(varText(i)) = varText(i)
Next
varOut = myDic.items
sWords = Join(varOut, " ")
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Old December 31st 20, 09:48 AM
Junior Member
 
First recorded activity by ExcelBanter: Dec 2020
Posts: 26
Default Try this simply

To filter for unique values, click Data Sort & Filter Advanced. To remove duplicate values, click Data Data Tools Remove Duplicates. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.

Regards,
Jerry


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Eliminate Duplicate Row Chip Excel Discussion (Misc queries) 1 September 25th 09 09:18 PM
how to eliminate duplicate numbers OPer Excel Worksheet Functions 5 September 11th 09 10:29 PM
Create a macro to eliminate certain words from several columns Jorge Excel Worksheet Functions 4 January 16th 08 09:15 PM
How to eliminate duplicate entries Tara Keane Excel Discussion (Misc queries) 4 March 2nd 05 06:33 PM
macro to eliminate spaces between words CSAM Excel Discussion (Misc queries) 3 December 17th 04 12:39 PM


All times are GMT +1. The time now is 08:33 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017