In the Select Duplicate & Unique Cells dialog box, select All unique(Including 1 st duplicates) option under the Rule section, see screenshot: Click Kutools > Select > Select Duplicate & Unique Cells, see screenshot:ģ. Select the list of cells that you want to combine only the unique values.Ģ. Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.Īfter installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now! )ġ. If you are not familiar with the VBA code, Kutools for Excel also can help you, you can combine the Select Duplicate & Unique Cells and Combine utilities to achieve the result you need. Note: In the above formula, A1:A17 is the cells range that you want to concatenate, and the comma “ ,” is the separator you need to separate the combined values, you can change them to your need. Then go back to your worksheet, and enter this formula: =ConcatUniq (A1:A17,',') into a blank cell where you want to output the concatenated result, and press Enter key to get the unique concatenated result, see screenshot: Set xDic = CreateObject('Scripting.Dictionary')ģ. VBA code: Concatenate unique values into a single cell: Function ConcatUniq(xRg As Range, xChar As String) As String Click Insert > Module, and paste the following code in the Module Window. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.Ģ. The following User Defined Function can help you to concatenate only the unique values into a single cell from a list, please do as follows:ġ. List unique values and concatenate corresponding values with Kutools for Excel List unique values and concatenate corresponding values with VBA code How could I deal with this problem quickly and easily in Excel?įind unique values in a column and concatenate them into one cell with VBA codeįind unique values in a column and concatenate them into one cell with Kutools for Excel If I have a long list of values which populated with some duplicate data, now, I want to find only the unique values and then concatenate them into a single cell. How to concatenate unique values in Excel?

Free kutools license name and code