Combining data with specific criteria

[IanPM]

I have a list of repeating references that I want to combine based on specific criteria in another sheet. Concatenated & transpose is probably part of the requirement but how do I refence it against a specific criteria not using VBA

Output expectation

Column A	Columnb
John	Slow, Fast
Karl	Fast,Ok,Slow

So I want to have a formula that can combine the below data in Column B based on criteria data in column A above, so that all Column B data is in single cell based on the Column A criteria. The below must be in summary & reflect as above

Below is the data

Column A	Column B
John	Slow
John	Fast
Karl	Fast
Karl	Ok
Karl	Slow

 

[steve the fish]

Easy if you have TEXTJOIN function:

=TEXTJOIN(",",TRUE,IF($A$1:$A$5=A1,$B$1:$B$5,""))

If you dont im not sure i can think of a way to do it with formula.

 

[IanPM]

Hi. I do not have TEXTJOIN. Are there any alternatives?

 

[sandy666]

another approach with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.TransformColumns(Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column B", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column B"),{{"Column B", Text.Trim, type text}})
in
    Result

 

[steve the fish]

CONCAT?

=LEFT(CONCAT(IF($A$1:$A$5=A1,$B$1:$B$5&",","")),LEN(CONCAT(IF($A$1:$A$5=A1,$B$1:$B$5&",","")))-1)

Other than that id have to use code as i know of no other way.

 

[IanPM]

Is this a VBA option? Outside of this nothing else?

 

[sandy666]

Is this a VBA option?

if that question is to me I can say: No, this is Power Query (Get&Transform), M-code
update your profile about Excel version. Less troubles.

btw. if TEXTJOIN is not available so CONCAT also

 

[MARK858]

A VBA version of Textjoin by the spreadsheetguru

Public Function TEXTJOIN(Delimiter As String, Ignore_Empty As Boolean, ParamArray Text1() As Variant) As String
'PURPOSE: Replicates The Excel 2016 Function TEXTJOIN
'SOURCE: www.TheSpreadsheetGuru.com

Dim RangeArea As Variant
Dim Cell As Range

'Loop Through Each Cell in Given Input
  For Each RangeArea In Text1
    If TypeName(RangeArea) = "Range" Then
      For Each Cell In RangeArea
        If Len(Cell.Value) <> 0 Or Ignore_Empty = False Then
          TEXTJOIN = TEXTJOIN & Delimiter & Cell.Value
        End If
      Next Cell
    Else
      'Text String was Entered
        If Len(RangeArea) <> 0 Or Ignore_Empty = False Then
          TEXTJOIN = TEXTJOIN & Delimiter & RangeArea
        End If
    End If
  Next RangeArea

TEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)

End Function

Link

Combining Text With The CONCAT and TEXTJOIN Excel Functions

There are a variety of reasons why you might want to combine various cell values into a single value. You might be trying to create a unique ID to perform a lookup or simply wanting to combine location information such as a city with a country. The Microsoft Excel developers have recognized the...

www.thespreadsheetguru.com

 

[MARK858]

Unfortunately I don't think the function that I posted works if array entered which the formula steve the fish posted requires to get the desired result.

 

[IanPM]

Mark858, do you have a suggestion on how to update as the option combines all values & seemingly only for 1 criteria not all