Grouping multiple values from a column into a row

[reporting_95]

Greetings everyone,

I am trying organize a list of items that were sold on different dates and at different prices. My goal is to summarize each item to show the sale price and date across one row for each item. I have an example of the list below.

Essentially, I'd like the list to show each item and its corresponding sale price along with the dates they sold across one row. Below is an example of what I am looking to accomplish.

I tried a vlookup formula but I wasn't able to put multiple values in the same column. For my example, I copied and pasted the items that corresponds with the sale price and date. My actual list is much longer.
Can you anyone please help?
Thanks!

 

[Peter_SSs]

Can you give us the sample data in a 'copyable' form? See my signature block below.

What version of Excel are you using? BTW, you can save this question being asked by updating your profile (Account details):

.. then it will show here

 

[sandy666]

if you are able to use Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Sale Price", Int64.Type}, {"Date Sold", type date}}),
    Group = Table.Group(Type, {"Item"}, {{"Count", each _, type table}}),
    ExtractSP = Table.TransformColumns(Table.AddColumn(Group, "Sale Price", each Table.Column([Count],"Sale Price")), {"Sale Price", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    ExtractDS = Table.TransformColumns(Table.AddColumn(ExtractSP, "Date Sold", each Table.Column([Count],"Date Sold")), {"Date Sold", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    ExtractDS

 

[reporting_95]

Hi Peter,

I updated my profile to indicate the versions of Excel I use. Also, below is a copyable version my example.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Item	Sale Price	Date Sold
2	Apple	$3	3/5/2017
3	Orange	$5	5/12/2018
4	Apple	$7	4/11/2017
5	Orange	$4	6/30/2018
6	Banana	$4	4/5/2019
7	Pear	$6	7/31/2019
8	Banana	$5	5/11/2019
9
10
11
12
13
14
15
Sheet2

Book1
	A	B	C	D	E	F	G	H
11	Item	Sale Prices	Sale Dates
12	Apple	$3, $7	3/5/2017, 4/11/2017
13	Orange	$5, $4	5/12/2018, 6/30/2018
14	Banana	$4, $5	4/5/2019, 5/11/2019
15	Pear	$6	7/31/2019
16
17
18
19
Sheet2

Thanks for the info!

Can you give us the sample data in a 'copyable' form? See my signature block below.

What version of Excel are you using? BTW, you can save this question being asked by updating your profile (Account details):
View attachment 1333
.. then it will show here
View attachment 1334

 

[Peter_SSs]

OK, thanks (saves some typing! )

Try these copied down, but note.

1. My dates are in d/m/y format so change the format in my column G formula to match what you want
2. Cols F & G are array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formulas can then be copied down.
3. Glitch with XL2BB has dropped the $ sign from F5

Book1
	A	B	C	D	E	F	G
1	Item	Sale Price	Date Sold		Item	Sale Prices	Sale dates
2	Apple	$3	5/03/2017		Apple	$3, $7	5/03/2017, 11/04/2017
3	Orange	$5	12/05/2018		Orange	$5, $4	12/05/2018, 30/06/2018
4	Apple	$7	11/04/2017		Banana	$4, $5	5/04/2019, 11/05/2019
5	Orange	$4	30/06/2018		Pear	6	31/07/2019
6	Banana	$4	5/04/2019
7	Pear	$6	31/07/2019
8	Banana	$5	11/05/2019
9
Group Values

Cell Formulas
Range	Formula
E2:E8	E2	=IFERROR(INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($E$1:E1,$A$2:$A$20)+(A$2:A$20=""),0),0)),"")
F2:F8	F2	{=IF(E2="","",TEXTJOIN(", ",1,IF($A$2:$A$20=E2,TEXT(B$2:B$20,"$0"),"")))}
G2:G8	G2	{=IF(E2="","",TEXTJOIN(", ",1,IF($A$2:$A$20=E2,TEXT(C$2:C$20,"d/mm/yyyy"),"")))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.