+ Reply to Thread
Results 1 to 10 of 10

Concatenate Unique Columns

  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

    Concatenate Unique Columns

    Hi All,

    I have multiple columns and need to concatenate the unique records from each into a single cell in each row. I am pretty sure I need to loop through the columns in each row until I reach an empty cell in the right of the range, but the problem I am running in to is that sometimes the records in non-adjacent cells match and I only want the value to show once. I have attached a sample file to show what I mean. I want to concatenate the unique class values into a single list in the far right column. Any help would be appreciated.

    Class Concatenate.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Concatenate Unique Columns

    Hi
    I have found a User Defined Function on another Forum that does this. Cut and paste this code into a module in your workbook:

    Function ConcatUniq(ByRef rng As Range, _
    ByVal myJoin As String) As String
    Dim r As Range
    Static dic As Object
    If dic Is Nothing Then _
    Set dic = CreateObject("Scripting.Dictionary")
    For Each r In rng
    dic(r.Value) = Empty
    Next
    ConcatUniq = Join$(dic.keys, myJoin)
    dic.RemoveAll
    End Function

    Then in a column to the right of your range insert the following formula in A1 for example and copy down for the number of rows you need.

    =ConcatUniq(B1:F1,",")

    Hope this helps.
    Tony

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Concatenate Unique Columns

    Hi Rebecca,
    maybe UDF
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Concatenate Unique Columns

    That worked great. However, I will be using this macro in another document. Will UDF still work?

  5. #5
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Concatenate Unique Columns

    Of course. Just copy the code in the standard module of your book, or copy the entire Module1.

  6. #6
    Registered User
    Join Date
    09-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Concatenate Unique Columns

    Perfect. Thanks!!!

  7. #7
    Registered User
    Join Date
    09-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Concatenate Unique Columns

    So I am running in to an issue. The Function and the Macro are located in one file, let's call is Macro.xlsm. The file I need to use the function in is located in another file, let's call it Data.xls. I cannot add a module to Data.xls, because it is auto-generated everyday. Basically, I need to be able to call the function from Macro.xlsm in order to use it in Data.xls. The other problem is that I will not know the location of Macro.xlsm, so if I need to refer to the file name, I won't have the path. Any ideas?

  8. #8
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Concatenate Unique Columns

    Rebecca, you can save this code in the personal macro workbook Personal.xlsb
    see for example here

  9. #9
    Registered User
    Join Date
    09-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Concatenate Unique Columns

    That's my problem, unfortunately. I need to reference it in the macro file only. I cannot access the Personal.xlsb file because it will be on a client's computer. I don't need the ability to access the function once the macro is complete. Do you know how I could make the macro without using a UDF? Thanks for the help.

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Concatenate Unique Columns

    Ok, here's the procedure that can be run from your file Macro.xlsm
    Please Login or Register  to view this content.
    Select the desired range (it can be entire columns) and run the macro. You will get the result in the far right column.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1