Concatenate without duplicates

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

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
 
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
 
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
 
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
Click to expand...

In view of Jacob's critique that the duplicates are considered to exist if one
is contained in another, (so that "range" is considered a duplicate since it is
contained within "Orange"), the following removes that:

=========================
Option Explicit
Function ConcatNonDups(rg) As String
'Adds a line feed and no dups or blanks
Dim cCol As Collection
Dim c As Range
For Each c In rg
If c.Text <> 0 And _
WorksheetFunction.CountIf(rg, c.Text) = 1 Then
ConcatNonDups = ConcatNonDups & c.Text & vbLf 'edit to suit
End If
Next c
ConcatNonDups = Left(ConcatNonDups, Len(ConcatNonDups) - 1)
End Function
=============================


Jacob's other critique is irrelevant since it is the last, and not the first,
character that needs to be removed.
--ron
 
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.
Click to expand...

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)
Click to expand...

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
 
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
Click to expand...

An extraneous line was in my previous submission. Corrected, and also changed
to comma-separation. Note that to change the separator to a ",<space>" we now
have to trim off 2 characters at the end instead of 1.

========================
Option Explicit
Function ConcatNonDups(rg) As String
'Adds a line feed and no dups or blanks
Dim c As Range
For Each c In rg
If c.Text <> 0 And _
WorksheetFunction.CountIf(rg, c.Text) = 1 Then
ConcatNonDups = ConcatNonDups & c.Text & ", "
'edit to suit
End If
Next c
ConcatNonDups = Left(ConcatNonDups, Len(ConcatNonDups) - 2)
End Function
===================================
--ron
 
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
 
Thanks for corrections Ron.

Never did properly test the original.

Thanks also to Jacob for pointing it out.


Gord
 
Back
Top