transpose data with same value in column A by date order

[rmx8000]

Hi,

I'm trying to think of a way to use VBA to transpose data with the same value in column A by sequential date order.

e.g starting with this:

CAR	date	cost	ref
VW	01/02/2013​	20​	ax1
FORD	01/01/2018​	22​	bx2
VW	01/08/2014​	21​	cx3
FORD	01/08/2017​	25​	cd3
AUDI	05/02/2016​	13​	cf4
AUDI	05/02/2020​	18​	cf5
VW	01/10/2016​	19​	cg6
AUDI	01/02/2014​	16​	gh4

The output I would like is this:

	date1	cost1	ref1	date2	cost2	ref2	date3	cost3	ref3
AUDI	01/02/2014​	16​	gh4	05/02/2016​	13​	cf4	05/02/2020​	18​	cf5
FORD	01/08/2016​	20​	df4	01/08/2017​	25​	cd3
VW	01/02/2013​	20​	ax1	01/08/2014​	21​	cx3	01/10/2016​	19​	cg6

Does anyone know a way to do this in excel / VBA?

 

[ExcelToDAX]

Assuming the table below is in Cells A1:D9, you can use the following function:

CAR	date	cost	ref
VW	1/2/2013	20	ax1
FORD	1/1/2018	22	bx2
VW	1/8/2014	21	cx3
FORD	1/8/2017	25	cd3
AUDI	5/2/2016	13	cf4
AUDI	5/2/2020	18	cf5
VW	1/10/2016	19	cg6
AUDI	1/2/2014	16	gh

=VSTACK(HSTACK("CAR",LET(CarBrands,A2:A9,UniqueBrand,SORT(UNIQUE(CarBrands)),ColumnsNames,{"date","cost","ref"},ColumnCount,LAMBDA(values,num_repeat,
XLOOKUP(SEQUENCE(SUM(num_repeat)),VSTACK(1,SCAN(1,num_repeat,LAMBDA(a,b,a+b))),VSTACK(values,""),,-1))(TRANSPOSE(ColumnsNames),SEQUENCE(COUNTA(UniqueBrand),1,MAX(COUNTIF(CarBrands,UniqueBrand)),0)),SortValue,LET(Val,MOD(SEQUENCE(COUNTA(ColumnCount),1,1,1),3),IF(Val=0,3,Val)),TRANSPOSE(SORTBY(ColumnCount,SortValue))&TRANSPOSE(SORT(SortValue)))),HSTACK(SORT(UNIQUE(A2:A9)),LET(FinalTable,LET(MainTable,LET(SortedDates,SORTBY(A2:D9,B2:B9),SORTBY(SortedDates,INDEX(SortedDates,,1))),DROP(IFERROR(REDUCE("",BYROW(SORT(UNIQUE(A2:A9)),LAMBDA(r,TEXTJOIN(", ",TRUE,FILTER(DROP(MainTable,,1),INDEX(MainTable,,1)=r)))),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,", ")))),""),1)),IF(LEN(FinalTable)=5,TEXT(FinalTable,"mm/dd/yyyy"),FinalTable))))

 

[rmx8000]

wow, that's pretty complex, but it works a treat.

Thank you very much for your help.

 

[Fluff]

Hi & welcome to MrExcel.
Another (slightly shorter) formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	CAR	date	cost	ref
2	VW	01/02/2013	20	ax1		AUDI	01/02/2014	16	gh	05/02/2016	13	cf4	05/02/2020	18	cf5
3	FORD	01/01/2018	22	bx2		FORD	01/08/2017	25	cd3	01/01/2018	22	bx2
4	VW	01/08/2014	21	cx3		VW	01/02/2013	20	ax1	01/08/2014	21	cx3	01/10/2016	19	cg6
5	FORD	01/08/2017	25	cd3
6	AUDI	05/02/2016	13	cf4
7	AUDI	05/02/2020	18	cf5
8	VW	01/10/2016	19	cg6
9	AUDI	01/02/2014	16	gh
Sheet6

Cell Formulas
Range	Formula
F2:O4	F2	=LET(u,SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))),m,MAX(COUNTIFS(A:A,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(SORT(FILTER(B2:D100,A2:A100=y))),,m*3,""))))),1))
Dynamic array formulas.

 

[rmx8000]

Thanks, thats a great solution too.

 

[Fluff]

You're welcome & thanks for the feedback.