Combining rows with multiple transactions on same dates

[tigerzen]

The spreadsheet illustrates what is needed, easy enough to do when the dates are different but need to be able to consolidate by date. The desired output is in Columns C and D. The user does not have Excel 365 and is using Excel 2016. I'm after a formula solution, helper columns are ok if no other solution exists. Power query/VBA solutions will do if the formulaic solution is not practical.

Book5
	A	B	C	D	E
1	Date	Description		Date	Description
2	1/01/2023	Room A hired 3 hours		1/01/2023	Room A hired 3 hours, Room B hired 2 hours
3		Room B hired 2 hours		2/01/2023	Room C hired 1 hour, Materials usage low, Room A hired 1 hour, Room E hired 1 hour, Room D hired 2 hours
4	2/01/2023	Room C hired 1 hour		4/01/2023	Room A hired 3 hours etc
5		Materials usage low
6	2/01/2023	Room A hired 1 hour
7		Room E hired 1 hour
8		Room D hired 2 hours
9	4/01/2023	Room A hired 3 hours
10		Room B hired 2 hours
11		Materials required
12		Order required
Sheet2

 

[Sanjeev1976]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Description", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Date"}, {{"Description", each Text.Combine([Description], ", "), type text}})
in
    #"Grouped Rows"

 

[Peter_SSs]

.. using Excel 2016. I'm after a formula solution, helper columns are ok ..

See if this would suit. I have used G & H as helper columns.

23 05 06.xlsm
	A	B	C	D	E	F	G	H
1	Date	Description
2	1/01/2023	Room A hired 3 hours		1/01/2023	Room A hired 3 hours, Room B hired 2 hours		1/01/2023	Room A hired 3 hours
3		Room B hired 2 hours		2/01/2023	Room C hired 1 hour, Materials usage low, Room A hired 1 hour, Room E hired 1 hour, Room D hired 2 hours, Other 1, Other 2		1/01/2023	Room A hired 3 hours, Room B hired 2 hours
4	2/01/2023	Room C hired 1 hour		4/01/2023	Room A hired 3 hours, Room B hired 2 hours, Materials required, Order required		2/01/2023	Room C hired 1 hour
5		Materials usage low					2/01/2023	Room C hired 1 hour, Materials usage low
6	2/01/2023	Room A hired 1 hour					2/01/2023	Room C hired 1 hour, Materials usage low, Room A hired 1 hour
7		Room E hired 1 hour					2/01/2023	Room C hired 1 hour, Materials usage low, Room A hired 1 hour, Room E hired 1 hour
8		Room D hired 2 hours					2/01/2023	Room C hired 1 hour, Materials usage low, Room A hired 1 hour, Room E hired 1 hour, Room D hired 2 hours
9	4/01/2023	Room A hired 3 hours					4/01/2023	Room A hired 3 hours
10		Room B hired 2 hours					4/01/2023	Room A hired 3 hours, Room B hired 2 hours
11		Materials required					4/01/2023	Room A hired 3 hours, Room B hired 2 hours, Materials required
12		Order required					4/01/2023	Room A hired 3 hours, Room B hired 2 hours, Materials required, Order required
13	2/01/2023	Other 1					2/01/2023	Room C hired 1 hour, Materials usage low, Room A hired 1 hour, Room E hired 1 hour, Room D hired 2 hours, Other 1
14		Other 2					2/01/2023	Room C hired 1 hour, Materials usage low, Room A hired 1 hour, Room E hired 1 hour, Room D hired 2 hours, Other 1, Other 2
Combine

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(AGGREGATE(15,6,G$2:G$14/ISNA(MATCH(G$2:G$14,D$1:D1,0)),1),"")
E2:E6	E2	=IF(D2="","",INDEX(H:H,AGGREGATE(14,6,ROW(H$2:H$14)/(G$2:G$14=D2),1)))
G2:G14	G2	=IF(A2="",G1,A2)
H2:H14	H2	=IF(A2="",H1&", "&B2,IFERROR(INDEX(H$1:H1,AGGREGATE(14,6,ROW(H$1:H1)/(G$1:G1=A2),1))&", ","")&B2)

 

[tigerzen]

Thanks Sanjeev and Peter, both solutions work fine. I've looked at the original data and feel that the users of the data would benefit by noting the instances of hiring. So as a follow up question to my original post what would we have to change in the solution to get a summary of what occurs each transaction, the desired output would be the same for both 1/01/2023 and the 4/01/2023 as there was only one transaction on those days, the complication would be for the 2/01/2023 and should be separately notated so that we get:
2/01/2023 Room C hired 1 hour, Materials usage low
2/01/2023 Room A hired 1 hour, Room E hired 1 hour, Room D hired 2 hours

I imagine this might be easier than the original question formula wise?

 

[Peter_SSs]

Try this

23 05 06.xlsm
	A	B	C	D	E	F	G
1	Date	Description
2	1/01/2023	Room A hired 3 hours		1/01/2023	Room A hired 3 hours, Room B hired 2 hours		Room A hired 3 hours, Room B hired 2 hours
3		Room B hired 2 hours		2/01/2023	Room C hired 1 hour, Materials usage low		Room B hired 2 hours
4	2/01/2023	Room C hired 1 hour		2/01/2023	Room A hired 1 hour, Room E hired 1 hour, Room D hired 2 hours		Room C hired 1 hour, Materials usage low
5		Materials usage low		4/01/2023	Room A hired 3 hours, Room B hired 2 hours, Materials required, Order required		Materials usage low
6	2/01/2023	Room A hired 1 hour		2/01/2023	Other 1		Room A hired 1 hour, Room E hired 1 hour, Room D hired 2 hours
7		Room E hired 1 hour		3/01/2023	Other 2		Room E hired 1 hour, Room D hired 2 hours
8		Room D hired 2 hours					Room D hired 2 hours
9	4/01/2023	Room A hired 3 hours					Room A hired 3 hours, Room B hired 2 hours, Materials required, Order required
10		Room B hired 2 hours					Room B hired 2 hours, Materials required, Order required
11		Materials required					Materials required, Order required
12		Order required					Order required
13	2/01/2023	Other 1					Other 1
14	3/01/2023	Other 2					Other 2
Combine (2)

Cell Formulas
Range	Formula
D2:D8	D2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$14)/(A$2:A$14<>""),ROWS(D$2:D2))),"")
E2:E8	E2	=IF(D2="","",INDEX(G:G,AGGREGATE(15,6,ROW(G$2:G$14)/(A$2:A$14=D2),COUNTIF(D$2:D2,D2))))
G2:G14	G2	=IF(A2<>"",B2&IF(AND(A3="",B3<>""),", "&G3,""),B2&IF(AND(A3="",B3<>""),", "&G3,""))

 

[tigerzen]

Thanks Peter, that works very well.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation on both scenarios.