Concatenate values in different rows if criteria cells match?

[iddy]

Hi

I'm hoping somebody can help me with a formula for this but all suggestions appreciated. (using Excel 2010 and Windows 7)

I have very large spreadsheets containing Vehicle Application data, the first line of which contains headings. All the other lines contain details of the spare part, the car it is fitted to, and any fitting restrictions.

Column......Column Name
A...........PART_ID
B...........PART_NAME
C...........TERMS_OF_USE
D...........VEHICLE

for rows when columns A,B,and C are the same I'd like to concatenate all the vehicles into a cell in Column E (separated by a colon)

This is an example of what I am looking to end up with (formula to go in column E to get that result)

PART_ID PART_NAME TERMS_OF_USE VEHICLE ALL VEHICLES
1152702 coil spring Lower to 35mm Ford Focus Ford Focus:Honda Civic:VW Golf
1152702 coil spring Lower to 35mm Honda Civic
1152702 coil spring Lower to 35mm VW Golf
1152702 coil spring Lower to 25mm Ford Focus II Ford Focus II:Honda Civic V:VW Golf IV
1152702 coil spring Lower to 25mm Honda Civic V
1152702 coil spring Lower to 25mm VW Golf IV
1152717 coil spring Toyota Avensis Toyota Avensis
1152728 Gas Spring, bonnet Ford Fiesta Ford Fiesta

Thanks for your time

 

[cyrilbrd]

Hi not sure if what you needed:

Given in A1:

Excel 2013
	A	B	C	D	E
1	PART_ID	PART_NAME	TERMS_OF_USE	VEHICLE	CONCAT
2	1152702	J	A	W	W,J,D
3	1152702	J	A	J	W,J,D
4	1152702	J	A	D	W,J,D
5	1152702	L	A	D	D,G
6	1152702	K	A	F	F
7	1152702	L	A	G	D,G
8	1152717	J	B	H	H
9	1152728	J	C	Q	Q
iddy

Cell Formulas
Range	Formula
E2	=ConcatIfs($D$2:$D$9,",",$A$2:$A$9,"="&A2,$B$2:$B$9,"="&B2,$C$2:$C$9,"="&C2)

code is:

Function ConcatIfs(ByVal stringsRange As Range, Delimiter As String, _                            ByVal compareRange1 As Range, ByVal Criteria1 As Variant, _
                            Optional ByVal compareRange2 As Range, Optional ByVal Criteria2 As Variant, _
                            Optional ByVal compareRange3 As Range, Optional ByVal Criteria3 As Variant, _
                            Optional ByVal compareRange4 As Range, Optional ByVal Criteria4 As Variant, _
                            Optional ByVal compareRange5 As Range, Optional ByVal Criteria5 As Variant, _
                            Optional NoDuplicates As Boolean) As String
    'created by mikerickson
    Dim i As Long, j As Long
    With compareRange1.Parent
       Set compareRange1 = Application.Intersect(compareRange1, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange1 Is Nothing Then Exit Function
    If compareRange2 Is Nothing Then
        Set compareRange2 = compareRange1
        Criteria2 = Criteria1
    End If
    If compareRange3 Is Nothing Then
        Set compareRange3 = compareRange1
        Criteria3 = Criteria1
    End If
    If compareRange4 Is Nothing Then
        Set compareRange4 = compareRange1
        Criteria4 = Criteria1
    End If
    If compareRange5 Is Nothing Then
        Set compareRange5 = compareRange1
        Criteria5 = Criteria1
    End If
    Set stringsRange = compareRange1.Offset(stringsRange.Row - compareRange1.Row, _
                                                stringsRange.Column - compareRange1.Column)
    Set compareRange2 = compareRange1.Offset(compareRange2.Row - compareRange1.Row, _
                                                compareRange2.Column - compareRange1.Column)
    
        For i = 1 To compareRange1.Rows.Count
            For j = 1 To compareRange1.Columns.Count
                If (Application.CountIf(compareRange1.Cells(i, j), Criteria1) = 1) _
                    And (Application.CountIf(compareRange2.Cells(i, j), Criteria2) = 1) _
                    And (Application.CountIf(compareRange3.Cells(i, j), Criteria3) = 1) _
                    And (Application.CountIf(compareRange4.Cells(i, j), Criteria4) = 1) _
                    And (Application.CountIf(compareRange5.Cells(i, j), Criteria5) = 1) Then
                    If InStr(ConcatIfs, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIfs = ConcatIfs & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        ConcatIfs = Mid(ConcatIfs, Len(Delimiter) + 1)
End Function

 

[iddy]

Thanks very much for that. It works even when the data is not sorted correctly which is a great added bonus!
Just one more question on the ranges in the formula. My worksheets are typically over 600k rows, with the value in Part_Id typically repeating for 10k rows - so I might have only 10k rows that I need to check together at one time. Am I correct in assuming I can change the absolute values in the formula to the beginning and end of the range and not break the formula? e.g. if my range starts on row 100 and ends on row 200 I can change $D$2:$D$9 to $D$100:$D$200

Thanks again

 

[cyrilbrd]

Most welcome, glad it worked for you.
Yes you may change the range to fit your model, just try to avoid selecting the whole column (D:D) as this may slow down the system.

 

[iddy]

Hi again,

I tried to put this into a Personal macro workbook to allow me to use it for my real workbooks and I'm having trouble getting it to save.
Not sure whats changed since I tried this when you first posted it but I am getting a compile error:invalid character now (I am copying the code from the forum into Visual Basic on the Developer tab.

All of the following code is showing in red with the first underscore highlighted (Function ConcatIfs(ByVal stringsRange As Range, Delimiter As String, _)

Function ConcatIfs(ByVal stringsRange As Range, Delimiter As String, _                            ByVal compareRange1 As Range, ByVal Criteria1 As Variant, _
                            Optional ByVal compareRange2 As Range, Optional ByVal Criteria2 As Variant, _
                            Optional ByVal compareRange3 As Range, Optional ByVal Criteria3 As Variant, _
                            Optional ByVal compareRange4 As Range, Optional ByVal Criteria4 As Variant, _
                            Optional ByVal compareRange5 As Range, Optional ByVal Criteria5 As Variant, _
                            Optional NoDuplicates As Boolean) As String

Thanks in advance for the help.

 

[cyrilbrd]

maybe:

Function ConcatIfs(ByVal stringsRange As Range, Delimiter As String, _                            
                            ByVal compareRange1 As Range, ByVal Criteria1 As Variant, _
                            Optional ByVal compareRange2 As Range, Optional ByVal Criteria2 As Variant, _
                            Optional ByVal compareRange3 As Range, Optional ByVal Criteria3 As Variant, _
                            Optional ByVal compareRange4 As Range, Optional ByVal Criteria4 As Variant, _
                            Optional ByVal compareRange5 As Range, Optional ByVal Criteria5 As Variant, _
                            Optional NoDuplicates As Boolean) As String

 

[iddy]

Thanks again, much appreciated

 

[cyrilbrd]

No worries, thanks for the feedback.