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,231
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,770
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,231
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,765
Messages
5,361,156
Members
400,615
Latest member
inzimam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top