Help combining values in excel

[tevan]

[TABLE="width: 245"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]apples[/TD]
[/TR]
[TR]
[TD]Mary [/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]grapes[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]lemon[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]celery[/TD]
[/TR]
[TR]
[TD]Mary [/TD]
[TD]grapes[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]celery[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]apples,oranges,lemon,celery[/TD]
[/TR]
[TR]
[TD]Mary [/TD]
[TD]oranges,grapes[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]grapes,oranges,[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]oranges,celery
[/TD]
[/TR]
</tbody>[/TABLE]

I have a spreadsheet in which I need to combine items listed in several different rows but belong to the same person. Can someone assist in getting on output as I show below? thank you in advance

 

[wideboydixon]

In Excel 2016, you can use TEXTJOIN() to do exactly this. Failing that, the easiest way is with VBA. If you want a formula-only version in a version prior to Excel 2016 then it's not easily done:

Book1
	A	B	C	D	E	F
1	Name	Item		John	apples,oranges,lemon,celery	apples,oranges,lemon,celery
2	John	apples		Mary	oranges,grapes	oranges,grapes
3	Mary	oranges		Bob	grapes,oranges	grapes,oranges
4	Bob	grapes		Sue	oranges,celery	oranges,celery
5	Sue	oranges
6	John	oranges
7	John	lemon
8	John	celery
9	Mary	grapes
10	Bob	oranges
11	Sue	celery
Sheet1

Cell Formulas
Range	Formula
F1	=ConsolidateList($D1,$A$2:$B$11,1,2)
E1	{=TEXTJOIN(",",TRUE,IF($A$2:$A$11=$D1,$B$2:$B$11,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Public Function ConsolidateList(searchTerm As String, sourceRange As Range, searchColumn As Long, resultColumn As Long) As String

Dim i As Long

ConsolidateList = ""

For i = 1 To sourceRange.Rows.Count
    If sourceRange.Cells(i, searchColumn).Value = searchTerm Then ConsolidateList = ConsolidateList & "," & sourceRange.Cells(i, resultColumn).Value
Next i
  
If Len(ConsolidateList) > 1 Then ConsolidateList = Mid(ConsolidateList, 2)

End Function

WBD

 

[sheetspread]

This works too:

Excel 2010
	A	B
1	Name	Item
2	John	apples
3	Mary	oranges
4	Bob	grapes
5	Sue	oranges
6	John	oranges
7	John	lemon
8	John	celery
9	Mary	grapes
10	Bob	oranges
11	Sue	celery
12
13	John	apples,oranges,lemon,celery
14	Mary	oranges,grapes
15	Bob	grapes,oranges
16	Sue	oranges,celery
Sheet2

Cell Formulas
Range	Formula
B13	=condtextjoin($A$2:$A$11,A13,$B$2:$B$11,",")

Function condtextjoin(rng As Range, rng2 As Range, rng3 As Range, delimiter As String)
Dim myarray As Variant
myarray = Evaluate("=IF((" & rng.Address & ")=" & rng2.Address & "," & rng3.Address & ")")
condtextjoin = Replace(Replace(Join(Application.Transpose(myarray), delimiter), delimiter & "False", ""), "False" & delimiter, "")
End Function