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 here:
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 <(E-Mail Removed)> 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 <(E-Mail Removed)> 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 <(E-Mail Removed)> 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
<(E-Mail Removed)> 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 <(E-Mail Removed)> 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
> <(E-Mail Removed)> 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 <(E-Mail Removed)>
wrote:

>On Thu, 1 Oct 2009 18:52:01 -0700, QB <(E-Mail Removed)> 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