Concatenating unique values in a cell

[Matharu]

Hi,
I have some data in a sheet and i want the last cell in the row to contain the unique values in that row concatenated. How can it be done?

This is what i want

<TABLE style="WIDTH: 478pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=637 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 384pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" width=512 colSpan=8 height=18>Row</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 94pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: silver" width=125>Result that I want</TD></TR><TR style="HEIGHT: 51.75pt" height=69><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 51.75pt; BACKGROUND-COLOR: transparent" height=69>a</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">b</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">b</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">c</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">c</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">a</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">a</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">d</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 94pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=125>a
b
c
d</TD></TR></TBODY></TABLE>


Thanking in advance..

 

[Legacy 68668]

=JoinUniq(A$1:E$1,A2:E2,Char(10))
Copy down.

Function JoinUniq(ByRef rng1 As Range, ByRef rng2 As Range, _
               ByVal myJoin As String) As String
Dim a, b, i As Long, e
a = rng1.Value : b = rng2.Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(b, 2)
        .item(b(1, i)) = .item(b(1, i)) & IIf(.item(b(1, i)) <> "", ",") & a(1, i)
    Next
    Each e In .keys
        JoinUniq = JoinUniq & myJoin & .item(e) & ": " & e
    Next
End With
JoinUniq = Mid$(JoinUniq, Len(myJoin) + 1)
End Function

 

[Matharu]

Thanks a lot once again Seiya. I really appreciate your efforts in solving my problem.