Concatenate without duplicates
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.
--To trim the first character off you can use mid(ConcatNonDups,2) instead of
Left(ConcatNonDups, Len(ConcatNonDups) - 1)
and few points on the below UDF
--Default delimiter would be space unless specified
=CONCATRANGE(A1:A10)
--By default blanks will be considered. The below would ignore blanks
=CONCATRANGE(A1:A10,",",1)
--By default duplicates will be combined. The below would avoid duplicates
and blanks
=CONCATRANGE(A1:A10,",",1,1)
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, 2)
End Function
If this post helps click Yes
---------------
Jacob Skaria
"Gord Dibben" wrote:
Copy/paste this UDF to a general module in your workbook.
Function ConcatNonDups(rg) As String
'Ron Rosenfield July 26, 2007
'Adds a line feed and no dups or blanks
Dim c As Range
For Each c In rg
If c.text < 0 And _
InStr(1, ConcatNonDups, c.text, vbTextCompare) = 0 Then
ConcatNonDups = ConcatNonDups & c.text & vbLf 'edit to suit
End If
Next c
ConcatNonDups = Left(ConcatNonDups, Len(ConcatNonDups) - 1)
End Function
You can change the de-limiters from linefeeds(vbLf) to comma "," or
space " " or your choice.
Gord Dibben MS Excel MVP
On Thu, 1 Oct 2009 18:52:01 -0700, QB wrote:
I have a table with cols A through J. I need to place in col J a
concatenation of Cols B through I, but without duplication as the same entry
could appear in multiple cols.
How can this be done?
Thank you,
QB
|