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
41,808
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,315
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
41,808
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,078,469
Messages
5,340,507
Members
399,380
Latest member
rovius

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top