Extract Date sorted list from multiple columns stored in a table

[vampsthevampyre]

Afternoon All,

I thought it would be a good idea to have my data inputted horizontally instead of vertically as I got bored of scrolling to the bottom of a table to add more data and it was easy to see the transactions for each company's shares. Now I have the issue that I cannot easily see what the last share transactions were (Make one job easy just to make a subsequent job ten times harder - seems to be a trend in the way I work).

If anybody can help I'm looking to create a list similar to C4 but also pulls in the data from not only from column I4 (Date1)but L4, O4, S4 (Date2,3 and 4 respectively) and potentially other columns as the data table increases over time.

The table will always consist of the headers (Name, Date, Shares and ShareCost) with the suffix of 1,2,3 etc.

The new table would look a bit like this

Name	Date	Shares	Cost
HLMA.LSE	23/01/2023	45	2141.64
SPX.LSE	23/01/2023	8	11543.6
BME.LSE	14/02/2023	207	476.69
SGE.LSE	27/02/2023	130	755.71
HLMA.LSE	07/03/2023	-45	2141.64
SPX.LSE	07/03/2023	-8	11543.6

Any help would be gratefully received

Regards
Ian

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3			Name	Date	Shares	Cost		Name	Date1	Shares1	ShareCost1	Date2	Shares2	ShareCost2	Date3	Shares3	ShareCost3	Date4	Shares4	ShareCost4
4			HLMA.LSE	23/01/2023	45	2141.64		BA..LSE	12/05/2023	105	988.4	22/05/2023	-105	988.4
5			SPX.LSE	23/01/2023	8	11543.6		BATS.LSE	13/03/2023	45	3040.5	16/05/2023	35	2738.5277
6			BME.LSE	14/02/2023	207	476.69		BME.LSE	14/02/2023	207	476.69	12/06/2023	28	563.1786
7			SGE.LSE	27/02/2023	130	755.71		CRDA.LSE	12/05/2023	13	6835	22/05/2023	-13	6835
8			BATS.LSE	13/03/2023	45	3040.5		CGS.LSE	15/05/2023	261	376.5
9			CSN.LSE	13/03/2023	340	284		CSN.LSE	13/03/2023	340	284	16/05/2023	342	286.65	13/06/2023	18	283.3889
10			GLEN.LSE	13/03/2023	378	456.6		DCC.LSE	12/05/2023	20	4732.5
11			GSK.LSE	13/03/2023	20	1387		DGE.LSE	12/05/2023	4	3528.25
12			KETL.LSE	13/03/2023	956	92.8		GLEN.LSE	13/03/2023	378	456.6	16/05/2023	225	437.237	05/06/2023	15	435.8625
13			LGEN.LSE	13/03/2023	378	241.3		GSK.LSE	13/03/2023	20	1387	12/05/2023	44	1481.9
14			MNG.LSE	13/03/2023	471	207.5		HLMA.LSE	23/01/2023	45	2141.64	07/03/2023	-45	2141.64
15			MONY.LSE	13/03/2023	1300	232.4		KETL.LSE	13/03/2023	956	92.8
16			NG..LSE	13/03/2023	91	1064.25		LGEN.LSE	13/03/2023	378	241.3	16/05/2023	423	231.9098	13/06/2023	22	238.1818
17			PHNX.LSE	13/03/2023	153	598.6		LMP.LSE	15/05/2023	521	188.472
18			PHP.LSE	13/03/2023	1349	100.6		MGNS.LSE	22/05/2023	52	1857.2
19			PSN.LSE	13/03/2023	66	1217.25		MNG.LSE	13/03/2023	471	207.5	16/05/2023	492	199.696
20			RIO.LSE	13/03/2023	31	5462		MONY.LSE	13/03/2023	1300	232.4	13/06/2023	42	262.7143
21			THRL.LSE	13/03/2023	1204	70.95		NG..LSE	13/03/2023	91	1064.25
22			VOD.LSE	13/03/2023	2042	96.615		PHNX.LSE	13/03/2023	153	598.6	16/05/2023	170	575.37	13/06/2023	6	553.5
23			VTY.LSE	13/03/2023	129	762.25		PHP.LSE	13/03/2023	1349	100.6	13/06/2023	22	100.818
24			BA..LSE	12/05/2023	105	988.4		PSN.LSE	13/03/2023	66	1217.25
25			CRDA.LSE	12/05/2023	13	6835		RIO.LSE	13/03/2023	31	5462	16/05/2023	19	4966.5
26			DCC.LSE	12/05/2023	20	4732.5		SGE.LSE	27/02/2023	130	755.71	07/03/2023	-130	755.71
27			DGE.LSE	12/05/2023	4	3528.25		SPX.LSE	23/01/2023	8	11543.6	07/03/2023	-8	11543.6
28			ULVR.LSE	12/05/2023	68	4340		SRE.LSE	15/05/2023	1262	78.246
29			UU..LSE	12/05/2023	93	1077		THRL.LSE	13/03/2023	1204	70.95	16/05/2023	1190	82.573	13/06/2023	22	75.1364
30			CGS.LSE	15/05/2023	261	376.5		TND.LSE	22/05/2023	359	247.2
31			LMP.LSE	15/05/2023	521	188.472		ULVR.LSE	12/05/2023	68	4340
32			SRE.LSE	15/05/2023	1262	78.246		UU..LSE	12/05/2023	93	1077
33			MGNS.LSE	22/05/2023	52	1857.2		VOD.LSE	13/03/2023	2042	96.615	16/05/2023	1137	86.457	16/05/2023	512	85
34			TND.LSE	22/05/2023	359	247.2		VTY.LSE	13/03/2023	129	762.25	13/06/2023	5	753.8
35
36
Sheet1

Cell Formulas
Range	Formula
C4:F34	C4	=SORTBY(tblSharePurchases[[Name]:[ShareCost1]],tblSharePurchases[Date1],1)
Dynamic array formulas.

 

[Georgiboy]

Not sure if the below will help as I don't know how many columns there would be:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Date1	Shares1	ShareCost1	Date2	Shares2	ShareCost2	Date3	Shares3	ShareCost3		Name	Date	Shares	Cost
2	BA..LSE	12/05/2023	105	988.4	22/05/2023	-105	988.4					HLMA.LSE	23/01/2023	45	2141.64
3	BATS.LSE	13/03/2023	45	3040.5	16/05/2023	35	2738.5277					SPX.LSE	23/01/2023	8	11543.6
4	BME.LSE	14/02/2023	207	476.69	12/06/2023	28	563.1786					BME.LSE	14/02/2023	207	476.69
5	CRDA.LSE	12/05/2023	13	6835	22/05/2023	-13	6835					SGE.LSE	27/02/2023	130	755.71
6	CGS.LSE	15/05/2023	261	376.5								HLMA.LSE	07/03/2023	-45	2141.64
7	CSN.LSE	13/03/2023	340	284	16/05/2023	342	286.65	13/06/2023	18	283.3889		SGE.LSE	07/03/2023	-130	755.71
8	DCC.LSE	12/05/2023	20	4732.5								SPX.LSE	07/03/2023	-8	11543.6
9	DGE.LSE	12/05/2023	4	3528.25								BATS.LSE	13/03/2023	45	3040.5
10	GLEN.LSE	13/03/2023	378	456.6	16/05/2023	225	437.237	05/06/2023	15	435.8625		CSN.LSE	13/03/2023	340	284
11	GSK.LSE	13/03/2023	20	1387	12/05/2023	44	1481.9					GLEN.LSE	13/03/2023	378	456.6
12	HLMA.LSE	23/01/2023	45	2141.64	07/03/2023	-45	2141.64					GSK.LSE	13/03/2023	20	1387
13	KETL.LSE	13/03/2023	956	92.8								KETL.LSE	13/03/2023	956	92.8
14	LGEN.LSE	13/03/2023	378	241.3	16/05/2023	423	231.9098	13/06/2023	22	238.1818		LGEN.LSE	13/03/2023	378	241.3
15	LMP.LSE	15/05/2023	521	188.472								MNG.LSE	13/03/2023	471	207.5
16	MGNS.LSE	22/05/2023	52	1857.2								MONY.LSE	13/03/2023	1300	232.4
17	MNG.LSE	13/03/2023	471	207.5	16/05/2023	492	199.696					NG..LSE	13/03/2023	91	1064.25
18	MONY.LSE	13/03/2023	1300	232.4	13/06/2023	42	262.7143					PHNX.LSE	13/03/2023	153	598.6
19	NG..LSE	13/03/2023	91	1064.25								PHP.LSE	13/03/2023	1349	100.6
20	PHNX.LSE	13/03/2023	153	598.6	16/05/2023	170	575.37	13/06/2023	6	553.5		PSN.LSE	13/03/2023	66	1217.25
21	PHP.LSE	13/03/2023	1349	100.6	13/06/2023	22	100.818					RIO.LSE	13/03/2023	31	5462
22	PSN.LSE	13/03/2023	66	1217.25								THRL.LSE	13/03/2023	1204	70.95
23	RIO.LSE	13/03/2023	31	5462	16/05/2023	19	4966.5					VOD.LSE	13/03/2023	2042	96.615
24	SGE.LSE	27/02/2023	130	755.71	07/03/2023	-130	755.71					VTY.LSE	13/03/2023	129	762.25
25	SPX.LSE	23/01/2023	8	11543.6	07/03/2023	-8	11543.6					BA..LSE	12/05/2023	105	988.4
26	SRE.LSE	15/05/2023	1262	78.246								CRDA.LSE	12/05/2023	13	6835
27	THRL.LSE	13/03/2023	1204	70.95	16/05/2023	1190	82.573	13/06/2023	22	75.1364		DCC.LSE	12/05/2023	20	4732.5
28	TND.LSE	22/05/2023	359	247.2								DGE.LSE	12/05/2023	4	3528.25
29	ULVR.LSE	12/05/2023	68	4340								ULVR.LSE	12/05/2023	68	4340
30	UU..LSE	12/05/2023	93	1077								UU..LSE	12/05/2023	93	1077
31	VOD.LSE	13/03/2023	2042	96.615	16/05/2023	1137	86.457	16/05/2023	512	85		GSK.LSE	12/05/2023	44	1481.9
32	VTY.LSE	13/03/2023	129	762.25	13/06/2023	5	753.8					CGS.LSE	15/05/2023	261	376.5
33												LMP.LSE	15/05/2023	521	188.472
34												SRE.LSE	15/05/2023	1262	78.246
35												BATS.LSE	16/05/2023	35	2738.528
36												CSN.LSE	16/05/2023	342	286.65
37												GLEN.LSE	16/05/2023	225	437.237
38												LGEN.LSE	16/05/2023	423	231.9098
39												MNG.LSE	16/05/2023	492	199.696
40												PHNX.LSE	16/05/2023	170	575.37
41												RIO.LSE	16/05/2023	19	4966.5
42												THRL.LSE	16/05/2023	1190	82.573
43												VOD.LSE	16/05/2023	1137	86.457
44												VOD.LSE	16/05/2023	512	85
45												MGNS.LSE	22/05/2023	52	1857.2
46												TND.LSE	22/05/2023	359	247.2
47												BA..LSE	22/05/2023	-105	988.4
48												CRDA.LSE	22/05/2023	-13	6835
49												GLEN.LSE	05/06/2023	15	435.8625
50												BME.LSE	12/06/2023	28	563.1786
51												MONY.LSE	13/06/2023	42	262.7143
52												PHP.LSE	13/06/2023	22	100.818
53												VTY.LSE	13/06/2023	5	753.8
54												CSN.LSE	13/06/2023	18	283.3889
55												LGEN.LSE	13/06/2023	22	238.1818
56												PHNX.LSE	13/06/2023	6	553.5
57												THRL.LSE	13/06/2023	22	75.1364
Sheet1

Cell Formulas
Range	Formula
L2:O57	L2	=LET( nm,tbl[Name], data,VSTACK(HSTACK(nm,tbl[[Date1]:[ShareCost1]]),HSTACK(nm,tbl[[Date2]:[ShareCost2]]),HSTACK(nm,tbl[[Date3]:[ShareCost3]])), SORT(FILTER(data,INDEX(data,,2)>0),2,1) )
Dynamic array formulas.

 

[vampsthevampyre]

@Georgiboy thanks for that it works, but being lazy I was looking for a solution that would automatically grow if the input table grows without me having to change the formula. Your solution does work perfectly and I will use it for the time being.

Thanks for your help

Ian

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Date1	Shares1	ShareCost1	Date2	Shares2	ShareCost2	Date3	Shares3	ShareCost3		Name	Date	Shares	Cost
2	BA..LSE	12/05/2023	105	988.40	22/05/2023	-105	988.40					HLMA.LSE	23/01/2023	45	2141.64
3	BATS.LSE	13/03/2023	45	3040.50	16/05/2023	35	2738.53					SPX.LSE	23/01/2023	8	11543.60
4	BME.LSE	14/02/2023	207	476.69	12/06/2023	28	563.18					BME.LSE	14/02/2023	207	476.69
5	CRDA.LSE	12/05/2023	13	6835.00	22/05/2023	-13	6835.00					SGE.LSE	27/02/2023	130	755.71
6	CGS.LSE	15/05/2023	261	376.50								HLMA.LSE	07/03/2023	-45	2141.64
7	CSN.LSE	13/03/2023	340	284.00	16/05/2023	342	286.65	13/06/2023	18	283.39		SGE.LSE	07/03/2023	-130	755.71
8	DCC.LSE	12/05/2023	20	4732.50								SPX.LSE	07/03/2023	-8	11543.60
9	DGE.LSE	12/05/2023	4	3528.25								BATS.LSE	13/03/2023	45	3040.50
10	GLEN.LSE	13/03/2023	378	456.60	16/05/2023	225	437.24	05/06/2023	15	435.86		CSN.LSE	13/03/2023	340	284.00
11	GSK.LSE	13/03/2023	20	1387.00	12/05/2023	44	1481.90					GLEN.LSE	13/03/2023	378	456.60
12	HLMA.LSE	23/01/2023	45	2141.64	07/03/2023	-45	2141.64					GSK.LSE	13/03/2023	20	1387.00
13	KETL.LSE	13/03/2023	956	92.80								KETL.LSE	13/03/2023	956	92.80
14	LGEN.LSE	13/03/2023	378	241.30	16/05/2023	423	231.91	13/06/2023	22	238.18		LGEN.LSE	13/03/2023	378	241.30
15	LMP.LSE	15/05/2023	521	188.47								MNG.LSE	13/03/2023	471	207.50
16	MGNS.LSE	22/05/2023	52	1857.20								MONY.LSE	13/03/2023	1300	232.40
17	MNG.LSE	13/03/2023	471	207.50	16/05/2023	492	199.70					NG..LSE	13/03/2023	91	1064.25
18	MONY.LSE	13/03/2023	1300	232.40	13/06/2023	42	262.71					PHNX.LSE	13/03/2023	153	598.60
19	NG..LSE	13/03/2023	91	1064.25								PHP.LSE	13/03/2023	1349	100.60
20	PHNX.LSE	13/03/2023	153	598.60	16/05/2023	170	575.37	13/06/2023	6	553.50		PSN.LSE	13/03/2023	66	1217.25
21	PHP.LSE	13/03/2023	1349	100.60	13/06/2023	22	100.82					RIO.LSE	13/03/2023	31	5462.00
22	PSN.LSE	13/03/2023	66	1217.25								THRL.LSE	13/03/2023	1204	70.95
23	RIO.LSE	13/03/2023	31	5462.00	16/05/2023	19	4966.50					VOD.LSE	13/03/2023	2042	96.62
24	SGE.LSE	27/02/2023	130	755.71	07/03/2023	-130	755.71					VTY.LSE	13/03/2023	129	762.25
25	SPX.LSE	23/01/2023	8	11543.60	07/03/2023	-8	11543.60					BA..LSE	12/05/2023	105	988.40
26	SRE.LSE	15/05/2023	1262	78.25								CRDA.LSE	12/05/2023	13	6835.00
27	THRL.LSE	13/03/2023	1204	70.95	16/05/2023	1190	82.57	13/06/2023	22	75.14		DCC.LSE	12/05/2023	20	4732.50
28	TND.LSE	22/05/2023	359	247.20								DGE.LSE	12/05/2023	4	3528.25
29	ULVR.LSE	12/05/2023	68	4340.00								GSK.LSE	12/05/2023	44	1481.90
30	UU..LSE	12/05/2023	93	1077.00								ULVR.LSE	12/05/2023	68	4340.00
31	VOD.LSE	13/03/2023	2042	96.62	16/05/2023	1137	86.46	16/05/2023	512	85.00		UU..LSE	12/05/2023	93	1077.00
32	VTY.LSE	13/03/2023	129	762.25	13/06/2023	5	753.80					CGS.LSE	15/05/2023	261	376.50
33												LMP.LSE	15/05/2023	521	188.47
34												SRE.LSE	15/05/2023	1262	78.25
35												BATS.LSE	16/05/2023	35	2738.53
36												CSN.LSE	16/05/2023	342	286.65
37												GLEN.LSE	16/05/2023	225	437.24
38												LGEN.LSE	16/05/2023	423	231.91
39												MNG.LSE	16/05/2023	492	199.70
40												PHNX.LSE	16/05/2023	170	575.37
41												RIO.LSE	16/05/2023	19	4966.50
42												THRL.LSE	16/05/2023	1190	82.57
43												VOD.LSE	16/05/2023	1137	86.46
44												VOD.LSE	16/05/2023	512	85.00
45												BA..LSE	22/05/2023	-105	988.40
46												CRDA.LSE	22/05/2023	-13	6835.00
47												MGNS.LSE	22/05/2023	52	1857.20
48												TND.LSE	22/05/2023	359	247.20
49												GLEN.LSE	05/06/2023	15	435.86
50												BME.LSE	12/06/2023	28	563.18
51												CSN.LSE	13/06/2023	18	283.39
52												LGEN.LSE	13/06/2023	22	238.18
53												MONY.LSE	13/06/2023	42	262.71
54												PHNX.LSE	13/06/2023	6	553.50
55												PHP.LSE	13/06/2023	22	100.82
56												THRL.LSE	13/06/2023	22	75.14
57												VTY.LSE	13/06/2023	5	753.80
Master

Cell Formulas
Range	Formula
L2:O57	L2	=LET(a,HSTACK(TOCOL(IF(SEQUENCE(,(COLUMNS(Table2)-1)/3),Table2[Name])),WRAPROWS(TOCOL(DROP(Table2,,1)),3)),SORT(FILTER(a,INDEX(a,,2)<>""),2))
Dynamic array formulas.

 

[vampsthevampyre]

Once again thanks @Fluff perfect solution and suits my lazy lifestyle and automatically expands as I add columns to the original table

Ian

 

[Fluff]

Glad we could help & thanks for the feedback.