Count and concatenate strings.

[mistereri]

Hello guys,
I am using xl since years but I am stuck at one point which I can not give a solution.

I have cells as below:
Item1, Model1
Item1, Model2
Item1, Model3
Item2, Model2
Item2, Model4

I need to result as below:
Item1, Model1;Model2;Model3
Item2, Model2;Model4

I have tried countif to get the counted repeating "itemX" but I can not group the models together.

Please some help is highly appreciated.
Thank You
Jon

 

[mistereri]

Hello,
can someone give any clue?

I know up to finding the COUNTIF of all items.
I have a function in MACRO:

Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
 Result = Result & Cell.Value & Separator
Next Cell
CONCATENATEMULTIPLE = Left(Result, Len(Result) - 1)
End Function

So far is good but I need to drag the mouse for each time the countif number changes.
This part I need to make automatic.

 

[planetpj]

Try this

=ConcatMult(D1,$A$1:$A$5)

Where D1 houses the value that you are looking up like (Item1) and A1:A5 houses the range.

Public Function ConcatMult(key As String, rows As Range) As String
    ConcatMult = ""
    Dim c As Range
    For Each c In rows
        If Not IsEmpty(c) Then
            Dim parts
            parts = Split(c, ",")
            If UBound(parts) = 1 Then
                If parts(0) = key Then
                    If ConcatMult <> "" Then ConcatMult = ConcatMult + ","
                    ConcatMult = ConcatMult + parts(1)
                End If
            End If
        End If
    Next c
End Function

 

[Rick Rothstein]

I have cells as below:
Item1, Model1
Item1, Model2
Item1, Model3
Item2, Model2
Item2, Model4

Is the above data in one column or two columns (with the comma being the column separator)?

 

[lrobbo314]

Is the above data in one column or two columns (with the comma being the column separator)?

This question needs to be answered. If they are in 1 column, the below PowerQuery code will do what you are asking for. It does make 1 assumption being that the column in question has a header row, which for testing purposes I named "Data".

Data.1	Custom
Item1	Model1;Model2;Model3
Item2	Model2;Model4

<colgroup><col><col></colgroup><tbody>
</tbody>

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Data.1", "Data.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Data.2", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Data.1"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Data.2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"})
in
    #"Removed Columns"

 

[lrobbo314]

The previous post splits Item and model into separate columns. To match your original post, which looks like everything in 1 column, the code below will do that.

Custom.1

Item1,Model1;Model2;Model3

Item2,Model2;Model4

<colgroup><col></colgroup><tbody>
</tbody>

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Data.1", "Data.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Data.2", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Data.1"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Data.2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom.1", each Text.Combine({[Data.1],[Custom]},",")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Data.1", "Custom"})
in
    #"Removed Columns1"

 

[lrobbo314]

Same results different approach. I know some people aren't comfortable/familiar with Power Query.

Sub Test()
Dim Dict    As Object: Set Dict = CreateObject("Scripting.Dictionary")
Dim R       As Range: Set R = Range("A2:A9") 'Range where your list is
Dim AR()    As Variant
Dim SP()    As String


AR = R.Value


With Dict
    For i = 1 To UBound(AR)
        SP = Split(AR(i, 1), ", ")
        If .exists(SP(0)) Then
            Dict(SP(0)) = Dict(SP(0)) & ";" & SP(1)
        Else
            Dict.Add SP(0), SP(0) & ", " & SP(1)
        End If
    Next i
End With


Set R = Range("C2").Resize(Dict.Count, 1) 'Range to output results
R.Value = Application.Transpose(Array(Dict.items))


End Sub

 

[LFKim2018]

Same results different approach. I know some people aren't comfortable/familiar with Power Query.

Mr. lrobbo314
your code worked nicely.
in behalf of OP - thank you..

 

[mistereri]

The data are in 2 columns.
I thank You all for your solutions, I have used some of the replied solutions and work well, really helpful.