Excel 2007 : combing columns in a cell and separating with a coma

[nafri]

hi

i am trying to find a solution to this problem.I have looked at various post but can seem to put anything together to get the outcome i need

A1 model   B1  poduct
A2 pp2      B2  lop2
A3 pp2      B3  lop4
A4 pp3      B4  lop5
A5 pp3      B5  lop6
A6 pp3      B6  lop7 

A1 contains multiple similar model and b1 contains products what assosiate to that model, i want to print out in Coloumn C products which have same model number in Row A .separated by a coma.For example like this

A1 model   B1 product           C1
A2 pp2      B2  lop2               C2   lop2,lop4
A3 pp2      B3  lop4               C3 (stays empty as result is copied into C2)
A4 pp3      B4  lop5               C4 lop5,lop6.lop7
A5 pp3      B5  lop6               C5 (stays empty as result is copied into C4)
A6 pp3      B6  lop7               C6 (stays empty as result is copied into C4) 

The excel file has over 12000 rows and doing it by hand is taking way too long and the list changes daily.

Any help would be appreciated.

kind regards

Nafri

[JBeaucaire]

Hi Nafri, welcome to the forum.

What you're describing is called String Concatenation. It's not a normal built-in function in Excel, but enough people have written User Defined Functions to add to their worksheets that it is easy to do nowadays. Have you ever worked with a UDF? If not, this will be new but not difficult.

First, to add code, you're going to have to save your sheet as .xlsm format, remember that. Here's the code we're going to add to your worksheet:

Option Explicit

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like SUMIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10, C1, $B$1:$B$10, ",", True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

========
How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

=============
Now, let's say the data looks exactly like your sample, with Models in A2 and down and Product codes in B2 and down.

In C2, put this formula that uses your new UDF function:
=CONCATIF($A:$A, A2, $B:$B, ",", TRUE))

Copy that down. See how it reads a lot like a SUMIF() formula? The added 4th parameter is the delimiter you want to use, and the 5th TRUE/FALSE parameter is where you instruct the function to remove duplicates or not. If TRUE, the lop2 would only appear once if it were found multiple times as a match.

Now that formula caused a concatenated string to appear in every row, and you said you only wanted a string in the FIRST row of each new model #, so now that you've seen the base UDF in action, here's a longer formula to get the real result you wanted:

=IF(COUNTIF($A$1:A2, A2) > 1, "",CONCATIF($A:$A, A2, $B:$B, ",", TRUE))

Attached is a sample book so you can see it.