Concatenate Without Duplicates

[bigdaddy123]

My spreadsheet has 32 columns each with a city name in it, though some cells may be blank in the latter columns. Column A, B, and C may have the same city multiple times. I need to Concatenate this information into a single cell such as "New York, Philadelphia, Chicago, Atlanta," etc, without any duplicates. Of course, omitting any blank cells would be great, but not a must.

I have seen a few "VBA modules" on the web, but will admit my ignorance. I have never used one, so, if you are providing one, would you be so kind to go through the steps to implement this?

Thanks!

 

[mfoster100]

Thank you Peter for all your assistance. This worked perfectly.

 

[Peter_SSs]

Thank you Peter for all your assistance. This worked perfectly.

You're welcome. Glad it is what you wanted.

 

[tahri]

Give this UDF (user defined function) a try...

Function ConcatCities(RowRange As Range) As String
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  Const Delimiter = ", "
  For X = 1 To RowRange.Count
    ReturnVal = RowRange(X).Value
    If Len(RowRange(X).Value) Then If InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) = 0 Then Result = Result & Delimiter & ReturnVal
  Next
  ConcatCities = Mid(Result, Len(Delimiter) + 1)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,

=ConcatCities(A2:AF2)

The range must be contiguous and all within the same row (as shown above). Just specify the actual range you want for the argument to the function and then copy the above formula down as far as needed.

Hi Rick/Peter,

Thanks a lot for this code; it really saved me a lot of time. I would like however to know if it would be possible to tweak it as to concatenate the values in a specific order that I would define.

For example, if the values of the range are such as below (Table 1), I would like to arrange them in the order stated in table 2, so that the final concatenated string is: Brandon, Rachel, John, Paul

Table 1:
Paul
John
Brandon
Brandon
Rachel

Table 2:
1. Olivia
2. Gareth
3. Brandon
4. Rachel
5. John
6. Mark
7. Paul
8. Justin

Thanks,
TJ

 

[Rick Rothstein]

I have modified the function to add the range containing the list of names defining the actual order you want names concatenated in as a second argument. I also changed the function's name to OrderedNames. This range would be what you show as Table 2, but with just the names listed (no counting numbers attached to them). So if your list that you want processed was in cells A1:A99 and the list of names in the actual order you want them listed in is on a sheet named "DATA" in cells N1:N8, then you would call the function like this...

=ConcatCities(A1:A99,DATA!N1:N8)

Here is the function code...

[table="width: 500"]
[tr]
	[td]Function OrderedNames(Rng As Range, NameOrder As Range) As String
  Dim X As Long, Nm As Variant
  Dim CellVal As String, ReturnVal As String, TestString As String, Result As String
  Const Delimiter = ", "
  For X = 1 To Rng.Count
    ReturnVal = Rng(X).Value
    If Len(Rng(X).Value) Then If InStr(TestString & Delimiter, Delimiter & ReturnVal & Delimiter) = 0 Then TestString = TestString & Delimiter & ReturnVal
  Next
  For Each Nm In NameOrder
    If InStr(TestString & Delimiter, Nm & Delimiter) Then Result = Result & Delimiter & Nm
  Next
  OrderedNames = Mid(Result, Len(Delimiter) + 1)
End Function[/td]
	[td][/td]
	[td][/td]
	[td][/td]
	[td][/td]
	[td][/td]
	[td][/td]
	[td][/td]
	[td][/td]
	[td][/td]
	[td][/td]
[/tr]
[/table]

 

[tahri]

Thanks for the prompt reply, Rick! You're amazing!