Concatenate Without Duplicates

B

bigdaddy123

New Member
Joined
Aug 6, 2012
Messages
7
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!
 
Sort by date Sort by votes
Give this a try...
Code: 
[table="width: 500"]
[tr]
	[td]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 Not RowRange(X).EntireColumn.Hidden Then 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[/td]
[/tr]
[/table]
 
Upvote 0
Hi Rick,

Thank you so much, really appreciate your help!

The tiniest change and this worked perfectly for me. Just had to switch "EntireColumn" with "EntireRow"

If Not RowRange(X).EntireRow.Hidden Then

Thanks again



Rick Rothstein said:
Give this a try...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]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 Not RowRange(X).EntireColumn.Hidden Then 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[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Rick Rothstein said:
Give this UDF (user defined function) a try...

Code: 
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.
Click to expand...

Hi Rick,

I was wondering if you could please give me a code to arrange the concatenated result in alphanumeric order.

Thank you very much for that very quick and easy tutorial. This is the first time ever UDF for me and your code is so much help for me! I was also able to change the title and delimiter per my needs. And thanks in advance for this request.
 
Upvote 0
Help101 said:
Hi Rick,

I was wondering if you could please give me a code to arrange the concatenated result in alphanumeric order.
Click to expand...
Give this version of my code a try...
Code: 
[table="width: 500"]
[tr]
	[td]Function ConcatCities(RowRange As Range) As String
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String, SL As Object
  Const Delimiter = ", "
  Set SL = CreateObject("System.Collections.ArrayList")
  For X = 1 To RowRange.Count
    ReturnVal = RowRange(X).Value
    If Not RowRange(X).EntireColumn.Hidden Then If Len(ReturnVal) Then If InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) = 0 Then SL.Add ReturnVal
  Next
  SL.Sort
  ConcatCities = Join(SL.ToArray, ", ")
End Function[/td]
[/tr]
[/table]
 
Upvote 0
Rick Rothstein said:
Give this version of my code a try...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ConcatCities(RowRange As Range) As String
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String, SL As Object
  Const Delimiter = ", "
  Set SL = CreateObject("System.Collections.ArrayList")
  For X = 1 To RowRange.Count
    ReturnVal = RowRange(X).Value
    If Not RowRange(X).EntireColumn.Hidden Then If Len(ReturnVal) Then If InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) = 0 Then SL.Add ReturnVal
  Next
  SL.Sort
  ConcatCities = Join(SL.ToArray, ", ")
End Function[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi Rick,

Thanks. The code above worked for sorting out the cell contents. However, the duplicates started appearing unlike in your original code. How do we remove the duplicates (return only the unique values in the cell)? Thanks in advance.

Ken
 
Upvote 0
Help101 said:
The code above worked for sorting out the cell contents. However, the duplicates started appearing unlike in your original code. How do we remove the duplicates (return only the unique values in the cell)? Thanks in advance.
Click to expand...
Sorry, I forgot about the non-duplicates part...
Code: 
[table="width: 500"]
[tr]
	[td]Function ConcatCities(RowRange As Range) As String
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String, SL As Object
  Const Delimiter = ", "
  Set SL = CreateObject("System.Collections.ArrayList")
  For X = 1 To RowRange.Count
    ReturnVal = RowRange(X).Value
    If Not RowRange(X).EntireColumn.Hidden Then
      If Len(ReturnVal) Then
        If Not SL.contains(ReturnVal) Then
          SL.Add ReturnVal
        End If
      End If
    End If
  Next
  SL.Sort
  ConcatCities = Join(SL.ToArray, ", ")
  Set SL = Nothing
End Function[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Rick Rothstein said:
Sorry, I forgot about the non-duplicates part...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ConcatCities(RowRange As Range) As String
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String, SL As Object
  Const Delimiter = ", "
  Set SL = CreateObject("System.Collections.ArrayList")
  For X = 1 To RowRange.Count
    ReturnVal = RowRange(X).Value
    If Not RowRange(X).EntireColumn.Hidden Then
      If Len(ReturnVal) Then
        If Not SL.contains(ReturnVal) Then
          SL.Add ReturnVal
        End If
      End If
    End If
  Next
  SL.Sort
  ConcatCities = Join(SL.ToArray, ", ")
  Set SL = Nothing
End Function[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Thanks so much Rick. It works like a charm. Now I can sort the unique contents of a cell! The other code with duplicates is not a waste though. I would still be able to use it! You are a genius!!!
 
Upvote 0
Hi Rick, your UDF has been a big time saver and works well. I was wondering how I could use this based on a value in another column. For instance, in Column A is a SKU number, and I want to concatenate the values in one cell based on the SKU. Is that possible?

Any assistance would be greatly appreciated.
 
Upvote 0
mfoster100 said:
Hi Rick, your UDF has been a big time saver and works well. I was wondering how I could use this based on a value in another column. For instance, in Column A is a SKU number, and I want to concatenate the values in one cell based on the SKU. Is that possible?

Any assistance would be greatly appreciated.
Click to expand...
Welcome to the MrExcel board!

Any chance we could see a small set of dummy data and the expected results? My signature block below has a link for ways to do that so we can copy/paste to test with.
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Any chance we could see a small set of dummy data and the expected results? My signature block below has a link for ways to do that so we can copy/paste to test with.
Click to expand...

Here is the data

[TABLE="width: 1000"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:4717;width:97pt" width="129"> <col style="mso-width-source:userset;mso-width-alt:5558;width:114pt" width="152"> <col style="mso-width-source:userset;mso-width-alt:5339;width:110pt" width="146"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> </colgroup><tbody>[TR]
[TD="width: 129"]138-5232U4
140-4352U4MakeSki-DooModelGSX SPORT 500SS, GTX SPORT 500 SS, MX Z TNT 500SS (2), MX Z TRAIL 500 S.S.

<tbody>
[TD="width: 86"]

<colgroup><col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:4790;width:98pt" width="131"> <col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> <col style="mso-width-source:userset;mso-width-alt:10971;width:225pt" width="300"> </colgroup><tbody>
[TD="class: xl65, width: 86"]SKU

<colgroup><col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:4790;width:98pt" width="131"> <col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> <col style="mso-width-source:userset;mso-width-alt:10971;width:225pt" width="300"> </colgroup><tbody>
[TD="class: xl65, width: 86"][TABLE="class: grid, width: 500"]
<tbody>[TR]

[TD="class: xl65, width: 118"]Attribute 2 Name
[/TD]
[TD="class: xl65, width: 131"]Attribute 2 Value(s)
[/TD]
[TD="class: xl65, width: 115"]Attribute 3 Name
[/TD]
[TD="class: xl65, width: 300"]Attribute 3 Value(s)[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]MOUNTAIN MAX 800[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]VMAX-4 800[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]VX800AQA[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]MOUNTAIN MAX 800 ST[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]VMAX-4 800[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]VMAX-4 800[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]VMAX-4 ST[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]VMAX-4 ST[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]VMAX-4 750[/TD]

[TD="class: xl65"]138-5232U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Yamaha[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]VMAX-4[/TD]

[TD="class: xl65"]140-4352U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Ski-Doo[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]GSX SPORT 500SS[/TD]

[TD="class: xl65"]140-4352U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Ski-Doo[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]GTX SPORT 500 SS[/TD]

[TD="class: xl65"]140-4352U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Ski-Doo[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]MX Z TNT 500SS (2)[/TD]

[TD="class: xl65"]140-4352U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Ski-Doo[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]MX Z TRAIL 500 S.S.[/TD]

[TD="class: xl65"]140-4352U4[/TD]
[TD="class: xl65"]Make[/TD]
[TD="class: xl65"]Ski-Doo[/TD]
[TD="class: xl65"]Model[/TD]
[TD="class: xl65"]GSX SPORT 500SS[/TD]

</tbody>
[/TD]

</tbody>

[/TD]
[TD="class: xl65, width: 118"][/TD]
[TD="class: xl65, width: 131"][/TD]
[TD="class: xl65, width: 115"][/TD]
[TD="class: xl65, width: 300"][/TD]

[TD="class: xl65"]Here is the result
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][TABLE="class: grid, width: 500"]
<tbody>[TR]

[TD="width: 152"]Make[/TD]
[TD="width: 146"]Yamaha[/TD]
[TD="width: 59"]Model
[/TD]
[TD="width: 115"]FORMULA III 600, MOUNTAIN MAX 800, VMAX-4 800, VX800AQA, MOUNTAIN MAX 800 ST, VMAX-4 ST, VMAX-4 750, VMAX-4[/TD]

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 118"][/TD]
[TD="width: 131"][/TD]
[TD="width: 115"][/TD]
[TD="width: 300"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Data Validation List without duplicates
2 3 4
Replies
30
Views
1K
kvsrinivasamurthy
K
T
  • Question Question
Is there a way to use VSTACK to get a PivotTable range?
Replies
0
Views
69
TomCon
T
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
182
Kaper
Kaper
D
  • Solved
Formula to aggregate emails if values in other cells match certain text
Replies
3
Views
582
Marcelo Branco
M
B
  • Solved
Macro to Open new tab and duplicate the data from the template, and rename new tab from cell value of template.
Replies
3
Views
707
mumps
mumps

Forum statistics

Threads
1,226,798
Messages
6,193,063
Members
453,773
Latest member
bclever07

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top