Concatenate Based on specific values

ntc78741

New Member
Joined
Jul 9, 2017
Messages
3
I am trying to concatenate a string of text separated by a comma based on specific values.

you can see in the example I have attached that there is a column of parent skus (configurable) that match a column of child skus (simple). In the configurable row I need to concatenate all of the simple skus into the concatenate column separated by a comma. I need to do this for over 10,000 skus.



https://wetransfer.com/<wbr>downloads/<wbr>11ac08dbbd4bf9bfbab67f565c59e1<wbr>c220170709154430/<wbr>69499eb2c2c42a88b5483ff52c7b30<wbr>3d20170709154430/2871d8
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
The website you directed us to wants to install cookies and establish an ongoing relationship with me.
I'm not going to download from that site.
If you can post an example of your data lay-out and the desire result on this board, that would help us help you.
Also, what have you tried and what about those attempts didn't work?
 
Upvote 0
You could use this UDF. Put =IF(A2="configurable", ConcatIf(C:C, "*-"&D2&"-*",C:C,","), "") in C2 and drag down.

Code:
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
    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
 
Last edited:
Upvote 0
If you have TEXTJOIN on your system...

In F2 enter and copy down:

=IF($A2="configurable",TEXTJOIN(",",TRUE,OFFSET($C2,0,0,COUNTIFS(D:D,$D2))),"")
 
Upvote 0
You could use this UDF. Put =IF(A2="configurable", ConcatIf(C:C, "*-"&D2&"-*",C:C,","), "") in C2 and drag down.

Code:
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
    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



column f2 is the example of what I am trying to do, this gives me a "#name?"

thank you for putting that code together, sorry if there was any confusion of what i was trying to do.
 
Upvote 0
The results that I got match what was in your column F. Did you put the ConcatIf code in a normal module (not the sheet's code module)?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top