Remove duplicates from a single cell and concatenate a row?

[rjwalters]

I am trying to concatenate cells that are in a row , lets say A1-A6. Each cell has letters that correspond to a task, so I am trying to concatenate them into one cell, A8, then I want to remove duplicates in that cell. So if it is "A,B,S,A,C,B" I want the end result to be "A,B,S,C".


I have tried several VBA's but not having any luck so here I am at the mercy of the masters.

 

[Jerome]

Why can't you use if statements? Do you have to use VBA?


=IF(COUNTIF($A$1:A1,A1)=1,A1,"")&IF(COUNTIF($A$1:A2,A2)=1,","&A2,"")&IF(COUNTIF($A$1:A3,A3)=1,","&A3,"")&IF(COUNTIF($A$1:A4,A4)=1,","&A4, "")&IF(COUNTIF($A$5:A5,A5)=1,","&A5,"")&IF(COUNTIF($A$1:A6,A6)=1,","&A6,"")

 

[lhkittle]

Try this for a VBA solution.


'

Option Explicit


Sub ConConWComma()

' ConCol is a named your range of cells in column A

ActiveSheet.Range("ConCol").RemoveDuplicates Columns:=1, Header:=xlNo

Range("B1") = Join(Application.Transpose(Range(Range("A1"), _

Range("A" & Rows.Count).End(xlUp))), ", ") '" / ")

End Sub

'


Regards,

Howard

 

[shrivallabha]

Instead of making it Sub routine, a UDF can be created and then it can be used like standard worksheet functions.


Put the following code in a standard module.

[pre]

Public Function ConcatRemDupes(rng As Range) As String
Dim r As Range
For Each r In rng
If InStr(ConcatRemDupes, r.Value) = 0 Then ConcatRemDupes = ConcatRemDupes & " " & r.Value
Next r
ConcatRemDupes = Replace(Trim(ConcatRemDupes), " ", ", ")
End Function

[/pre]
And then in A8 write formula:

=ConcatRemDupes(A1:A6)

 

[Debraj]

Hi Shri..


I hope its better to use "," in place of " ".. if text is with space.. it will fail..

[pre]

Public Function ConcatRemDupes(rng As Range) As String
Dim r As Range
For Each r In rng
If InStr(ConcatRemDupes & ",", "," & Trim(r) & ",") = 0 Then ConcatRemDupes = ConcatRemDupes & "," & Trim(r)
Next r
ConcatRemDupes = Mid(ConcatRemDupes, 2)
End Function

[/pre]

Regards,

Deb

 

[shrivallabha]

Hi Deb,


That is good reinforcement.

 

[rjwalters]

I guess I am not understanding how to use the code. I put in a module and applied the formula to the cell as directed, but nothing. It will add letters to it but not remove duplicates? What I missing!

 

[shrivallabha]

Could you post sample data where it is not working?


It can be fake data but it should match your case perfectly.