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

 

[MARK858]

You need to change the cell reference for each criteria but as I stated in my last post the function needs to be array entered to work as Steve the fish has it and I don't know if there is anything we can do for that.
I'll have a look when I get back in later.

 

[IanPM]

Mark858. That will be great

 

[FormR]

If you are able to sort the lookup table by column A and are willing to use a helper column - then here is a formula based option you can try.

Lookup table:

Book1
	A	B	C
1	Column A	Column B	Helper Column
2	John	Slow	Slow
3	John	Fast	Slow, Fast
4	Karl	Fast	Fast
5	Karl	Ok	Fast, Ok
6	Karl	Slow	Fast, Ok, Slow
7	Fred	A	A
8	Fred	B	A, B
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(A2=A1,C1&", "&B2,B2)

Outcome:

Book1
	A	B
1	John	Slow, Fast
2	Karl	Fast, Ok, Slow
3	Fred	A, B
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=LOOKUP(2,1/(A1=Sheet1!$A$2:$A$8),Sheet1!$C$2:$C$8)

 

[IanPM]

As a backup I will keep it but the structure of the file & data will make it complicated

 

[MARK858]

Ok try the below, remember that the formula must be entered with Ctrl-Shift-Enter and not just Enter. I have also put a test file in the link.

Function TxtJoin(strLim As String, boolTst As Boolean, ParamArray varArg() As Variant) As Variant 'v2_02

    Dim strTemp As String, strArg As String, dblUB As Double, dblArg As Double, rngCell As Variant

    dblUB = UBound(varArg)

    For dblArg = 0 To dblUB
        strArg = TypeName(varArg(dblArg))

        If strArg = "Range" Or strArg = "Variant()" Then
            For Each rngCell In varArg(dblArg)
                If boolTst = True And rngCell = "" Then
                Else
                    strTemp = strTemp & CStr(rngCell) & strLim
                End If
            Next
        Else
            If boolTst = True And CStr(varArg(dblArg)) = "" Then
            Else
                strTemp = strTemp & CStr(varArg(dblArg)) & strLim
            End If
        End If

    Next

    If strArg = "Error" Then
        TxtJoin = CVErr(xlErrNA)
    Else
        strTemp = IIf(strTemp = "", strLim, strTemp)
        TxtJoin = Left(strTemp, Len(strTemp) - Len(strLim))
    End If

End Function

Book1
	A	B	C	D
1	Column A	Column B
2	John	Slow	John	Slow,Fast
3	John	Fast	Karl	Fast,Ok,Slow
4	Karl	Fast
5	Karl	Ok
6	Karl	Slow
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	{=TXTJOIN(",",TRUE,IF($A$2:$A$6=C2,$B$2:$B$6,""))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Link to test file (look at Sheet2)

 

[IanPM]

Worked great. Thank you