CODEWITHSUNDEEP
excel
dani herrera
dani herrera

Reputation: 51665

Concatenate different columns values in a cell

Let's supose 'large' excel like this:

A           |B    
ab          |ef   
            |oo
ut          |
            |oo
ut          |ef

That I need is a new row with a summary of all differents values of each column:

A           |B    
ab          |ef   
            |oo
ut          |
            |oo
ut          |ef
ab,ut       |ef,oo    <- new row with the 'summary'

Note: I can copy by hand the formula at end of each column, I only need the formula

Upvotes: 0

Views: 1381

Answers (2)

Kannan Suresh
Kannan Suresh

Reputation: 4580

Following is a function that can be used to Concatenate unique columns values in a cell

Function UniqueItem(InputRange As Range) As Variant
    Dim cl As Range, cUnique As New Collection, cValue As Variant
        Application.Volatile
        On Error Resume Next
        For Each cl In InputRange
            If cl.Formula <> "" Then
            cUnique.Add cl.Value, CStr(cl.Value)
            End If
        Next cl
        UniqueItem = ""
        For i = 1 To cUnique.Count
            If UniqueItem = "" Then
            UniqueItem = UniqueItem & cUnique(i)
            ElseIf UniqueItem <> "" Then
            UniqueItem = UniqueItem & ", " & cUnique(i)
            End If
        Next
        On Error GoTo 0
End Function

How to use this function 1. Open excel file 2. Press Alt + F11 3. Create a new module and paste the code in it 4. Go back to the excel file and select the cell you want to have the result 5. Enter formula as =UniqueItem(A1:A5) A1:A5 specifies the range. You can specify any range.

Please find the sample file at the following link: Concatenate_different_columns_values_in_a_cell.xlsm

Upvotes: 2

Robert Ilbrink
Robert Ilbrink

Reputation: 7953

With some tricks (and an extra column) you can do this the following way.

In column A row 1 through 6, I placed some random text. In column B I placed the following formulae.

B1: =IF(COUNTIF($A$1:$A1,A1)=1,A1&", ","")

B2: =IF(COUNTIF($A$1:$A2,A2)=1,A2&", ","")

B3: =IF(COUNTIF($A$1:$A3,A3)=1,A3&", ","")

B4: =IF(COUNTIF($A$1:$A4,A4)=1,A4&", ","")

B5: =IF(COUNTIF($A$1:$A5,A5)=1,A5&", ","")

B6: =IF(COUNTIF($A$1:$A6,A6)=1,A6&", ","")

B7: =SUBSTITUTE(B1&B2&B3&B4&B5&B6&",",", ,","")

The idea here is that the search list grows from row 1 to the end and marks any unique value. If the statement in B1 through B6 is true, the value from A is used and a , {space} is added. In B7 I just concatenate all values, then I add an extra comma at the end. The substitute removes the last ,{space}, with nothing, effectively making sure the list does not end with a comma.

You can paste the formula for B1 in cell B1 and then just paste the formula down. The relative reference automatically increases the search array.

Understand. One (not so elegant) way is the following... Set C1: = B1, the C2: =B1&B2, then copy C2 all the way down to the end.... On the other hand, I think this might be better served by a VBA solution.

Alternatively, you could use this...

http://mcgimpsey.com/excel/udfs/multicat.html

Regards,

Robert Ilbrink

Upvotes: 0

Related Questions