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

Hello,

Array-enter
=Multicat(INDEX(Lfreq(TRANSPOSE(B1:I1)),,1),",")

Multicat and Lfreq are UDF's which you can find he
http://sulprobil.com/html/concatenate.html
http://sulprobil.com/html/lfreq.html

Regards,
Bernd

PS: If you need to sort them as well: http://sulprobil.com/html/sort_vba.html

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

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

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

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

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

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

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
---------------
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

Thanks for corrections Ron.

Never did properly test the original.

Thanks also to Jacob for pointing it out.


Gord

On Fri, 02 Oct 2009 07:48:32 -0400, Ron Rosenfeld
wrote:

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

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