List data horizontally from table based on date

[Tipsey]

Hello everyone,

I have a table with orders in it and I would like to generate the desired output based on the return date using excel formulas and not VB.

Thanks in advance for any help you can provide.

Return Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Number	Name	Order Date	Return Date		Desired Output		Number	Name	Return 1	Return 2	Return 3
2	1	Bob	12-Jan	29-Jan				1	Bob	29-Jan	28-Feb
3	2	Charlie	4-Feb	1-Mar				2	Charlie	27-Feb	1-Mar	6-Mar
4	3	Doug	5-Feb					3	Doug
5	2	Charlie	20-Feb					4	Ray	7-Mar
6	2	Charlie	21-Feb
7	1	Bob	22-Feb	28-Feb
8	3	Doug	22-Feb
9	2	Charlie	25-Feb	27-Feb
10	1	Bob	28-Feb
11	4	Ray	1-Mar	7-Mar
12	2	Charlie	1-Mar	6-Mar
13	3	Doug	2-Mar
14
Sheet1

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Number	Name	Order Date	Return Date		Desired Output		Number	Name	Return 1	Return 2	Return 3
2	1	Bob	45303	45320				1	Bob	45320	45350
3	2	Charlie	45326	45352				2	Charlie	45352	45349	45357
4	3	Doug	45327					3	Doug
5	2	Charlie	45342					4	Ray	45358
6	2	Charlie	45343
7	1	Bob	45344	45350
8	3	Doug	45344
9	2	Charlie	45347	45349
10	1	Bob	45350
11	4	Ray	45352	45358
12	2	Charlie	45352	45357
13	3	Doug	45353
Data

Cell Formulas
Range	Formula
H2:I5	H2	=UNIQUE(A2:B13)
J2:K2,J4:J5,J3:L3	J2	=TOROW(FILTER($D$2:$D$13,($D$2:$D$13<>"")*($A$2:$A$13=H2),""))
Dynamic array formulas.

 

[alansidman]

An alternative with Power Query

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Number	Name	Order Date	Return Date
2	1	Bob	45303	1/29/2024		Number	Name	Return 1	Return 2	Return 3
3	2	Charlie	45326	3/1/2024		1	Bob	45303	45344
4	3	Doug	45327			2	Charlie	45326	45347	45352
5	2	Charlie	45342			4	Ray	45352
6	2	Charlie	45343
7	1	Bob	45344	2/28/2024
8	3	Doug	45344
9	2	Charlie	45347	2/27/2024
10	1	Bob	45350
11	4	Ray	45352	3/7/2024
12	2	Charlie	45352	3/6/2024
13	3	Doug	45353
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Return Date] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Return Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count", each _, type table [Number=number, Name=text, Order Date=number, Return Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Number", "Order Date", "Return Date", "Index"}, {"Number", "Order Date", "Return Date", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Return Date"}),
    #"Added Prefix" = Table.TransformColumns(#"Removed Columns1", {{"Index", each "Return " & Text.From(_, "en-US"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Order Date"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Number", "Name", "Return 1", "Return 2", "Return 3"})
in
    #"Reordered Columns"

 

[alansidman]

misread and mixed up the order data and return data. Apologies.