Concatenate without duplicates
Ron, you are correct and the corrected (duplicate handled) one looks good...
Did a small modification to the last line of mine so that the user can have
delimiter to be of any length.. like ", "
Function CONCATRANGE(rngRange As Range, _
Optional strDelimiter As String = " ", _
Optional blnAvoidBlank As Boolean = False, _
Optional blnAvoidDuplicates As Boolean = False)
Dim varTemp As Range, blnPass As Boolean
For Each varTemp In rngRange
blnPass = True
If blnAvoidBlank And Trim(varTemp) = vbNullString Then blnPass = False
If blnAvoidDuplicates Then
If InStr(1, CONCATRANGE & strDelimiter, strDelimiter & _
varTemp & strDelimiter, vbTextCompare) Then blnPass = False
End If
If blnPass Then CONCATRANGE = CONCATRANGE & strDelimiter & varTemp
Next
CONCATRANGE = Mid(CONCATRANGE, len(strDelimiter)+1)
End Function
If this post helps click Yes
---------------
Jacob Skaria
"Ron Rosenfeld" wrote:
On Thu, 1 Oct 2009 22:11:01 -0700, Jacob Skaria
wrote:
Hi Gord
Few points on the UDF
--Entries in sequence like orange, range will be considered as duplicates
which should be considered as different entries.
Agreed, and I've submitted a modification.
--To trim the first character off you can use mid(ConcatNonDups,2) instead of
Left(ConcatNonDups, Len(ConcatNonDups) - 1)
These are not equivalent.
Why would you want to trim the FIRST character?
Left(ConcatNonDups, Len(ConcatNonDups) - 1) trims the LAST character off, which
is appropriate for this routine.
--ron
|