Grouping multiple values from a column into a row

reporting_95

New Member
Joined
May 7, 2019
Messages
12
Office Version
365, 2019, 2016
Platform
Windows
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.
1575511923999.png


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.
1575512003749.png


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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,207
Office Version
365
Platform
Windows
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):
1575513999725.png

.. then it will show here
1575514146289.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,759
if you are able to use Power Query

grp.jpg


Code:
// 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

New Member
Joined
May 7, 2019
Messages
12
Office Version
365, 2019, 2016
Platform
Windows
Hi Peter,

I updated my profile to indicate the versions of Excel I use. Also, below is a copyable version my example.
xl2bb.xlam
ABCDEFGHIJK
1ItemSale PriceDate Sold
2Apple$3 3/5/2017
3Orange$5 5/12/2018
4Apple$7 4/11/2017
5Orange$4 6/30/2018
6Banana$4 4/5/2019
7Pear$6 7/31/2019
8Banana$5 5/11/2019
9
10
11
12
13
14
15
Sheet2



xl2bb.xlam
ABCDEFGH
11ItemSale PricesSale Dates
12Apple$3, $73/5/2017, 4/11/2017
13Orange$5, $45/12/2018, 6/30/2018
14Banana $4, $54/5/2019, 5/11/2019
15Pear$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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,207
Office Version
365
Platform
Windows
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
xl2bb.xlam
ABCDEFG
1ItemSale PriceDate SoldItemSale PricesSale dates
2Apple$3 5/03/2017
3Orange$5 12/05/2018
4Apple$7 11/04/2017
5Orange$4 30/06/2018
6Banana$4 5/04/2019
7Pear$6 31/07/2019
8Banana$5 11/05/2019
9
Group Values
Cell Formulas
Range(s)Formula
E2:E8E2=IFERROR(INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($E$1:E1,$A$2:$A$20)+(A$2:A$20=""),0),0)),"")
F2:F8F2{=IF(E2="","",TEXTJOIN(", ",1,IF($A$2:$A$20=E2,TEXT(B$2:B$20,"$0"),"")))}
G2:G8G2{=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.
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top