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!

 

[pplstuff]

This can be done with a formula, although it may be long. Can you explain your data a bit more?

You're saying 32 Columns (a:ae)?? And only ColA,B,C might have duplicates? And if there's a blank you want nothing? (So if there are 3 blanks in row 1, you'll only have 29 entries assuming there are no duplicates)

 

[Rick Rothstein]

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.

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.

 

[bigdaddy123]

Rick, This worked like a dream! Thank you for being amazingly smart and helping a UDF rookie out! You have saved me hours of work trying to make this happen. Quick question: Do I need to install this UDF on every spreadsheet that I use it on, or is it readily available on any Macros enabled spreadsheet?

Thanks again!

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.

 

[Rick Rothstein]

Rick, This worked like a dream! Thank you for being amazingly smart and helping a UDF rookie out! You have saved me hours of work trying to make this happen. Quick question: Do I need to install this UDF on every spreadsheet that I use it on, or is it readily available on any Macros enabled spreadsheet?

That kind of depends on exactly what you are doing. If you are giving your workbook out to several people, just installing it in the one workbook is sufficient... the code will travel with the workbook. If instead you mean you have multiple workbooks where you will do similar concatenations, then it has to be installed in each workbook. If the later, you can simplify the process slightly by exporting the module where you installed the code. Select the module in the VB editor so it is the active window and then click File/Export on the VB editor's menu bar, name the file (leave the "Save as type" alone), select a directory and Save it. You can then send that file to others or you can use it yourself on other workbooks of yours. To use it (either you or those you send it to), just import it into the workbook of your choice (File/Import on the VB editor's menu bar).

 

[bigdaddy123]

I was referring to the second scenario of having multiple workbooks. I will keep the code handy and install as needed. Again, thanks for the quick assist!

 

[CherylLohr]

Hi,
This is one of the best explanations of UDFs I've seen. Before I start ripping my hair out can someone tell me if I can just replace 'RowRange' with 'ColumnRange'.

I have a species biology spreadsheet with 100 odd species in it. Each species may have 1 or more rows associated with it. There are about 40 odd columns with different numeric and text parameters in them. I use a lot of nested if functions to summarise numeric data with the first condition being 'for species X'... Now I need to concatenate text parameters. Text in multiple cells within a column. First condition being A:A=species X. Ignore blanks and remove duplicates.

I know this thread is old, so any help will be greatly appreciated.

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.

 

[Rick Rothstein]

I have a species biology spreadsheet with 100 odd species in it. Each species may have 1 or more rows associated with it. There are about 40 odd columns with different numeric and text parameters in them. I use a lot of nested if functions to summarise numeric data with the first condition being 'for species X'... Now I need to concatenate text parameters. Text in multiple cells within a column. First condition being A:A=species X. Ignore blanks and remove duplicates.

I know this thread is old, so any help will be greatly appreciated.

From your description, it sounds like the UDF that I posted in the following mini-blog article of mine might do the trick for you...

<!-- title / author block --> [h=3]LookUp Value and Concatenate All Found Results[/h]

 

[CherylLohr]

Rick you are a genius teacher, thank you! The number of times I have stepped back from trying to use a UDF because they are confusing is huge! Your thorough explanations are invaluable!

Hi,
This is one of the best explanations of UDFs I've seen. Before I start ripping my hair out can someone tell me if I can just replace 'RowRange' with 'ColumnRange'.

I have a species biology spreadsheet with 100 odd species in it. Each species may have 1 or more rows associated with it. There are about 40 odd columns with different numeric and text parameters in them. I use a lot of nested if functions to summarise numeric data with the first condition being 'for species X'... Now I need to concatenate text parameters. Text in multiple cells within a column. First condition being A:A=species X. Ignore blanks and remove duplicates.

I know this thread is old, so any help will be greatly appreciated.

 

[DL5000]

Hi Rick,

The below UDF has worked really well and has saved me loads of time so would really like to thank you. I have one question on it however. Is it possible to add a component which ensures the returned concatenated value only includes visible cells when a filter has been applied to the column.

I have tried a number of things, SpecialCells(xlCellTypeVisible) etc... but can't seem to get the syntax right. Any ideas?

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