Possible Index Aggregate formula required

[ronie85]

Hi All

I require a formula using the below list that has been created, and using Aberdeen as an example, assuming they appear 50 times in the A column with their result in the B column.

I need a formula to record their first appearance on the above list using cell A2 on the below list, and show the "result" from the B column in the above list, into cell E2 below.
Then the "result" from their 2nd appearance on the above list to show within cell F2 below, and so on.

I will then use this formula in Row 3 for the next Team, repeating this.

 

[ExceLoki]

try this
-----------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	team	result		team	season	league	1	2	3	4	5	6	7
2	aberdeen	draw		aberdeen	22/23	s1	draw	void	lost	win
3	st johnstone	draw		st johnstone	22/23	s1	draw
4	airdrieonians	lost		void	22/23	s1	void	void
5	ayr united	win
6	void	void
7	void	void
8	inverness caledonian thistle	draw
9	patrick thistle	draw
10	queens park	draw
11	morton	draw
12	raith rovers	draw
13	dundee united	draw
14	cove rangers	win
15	annan athletic	lost
16	edinburrh city	lost
17	kelty hearts	win
18	montrose	win
19	alloa atheletic	lost
20	aberdeen	void
21	aberdeen	lost
22	aberdeen	win
Sheet1

Cell Formulas
Range	Formula
G2:J2,G4:H4,G3	G2	=TRANSPOSE(FILTER($B$2:$B$22,$A$2:$A$22=D2,""))
Dynamic array formulas.

 

[ronie85]

try this
-----------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	team	result		team	season	league	1	2	3	4	5	6	7
2	aberdeen	draw		aberdeen	22/23	s1	draw	void	lost	win
3	st johnstone	draw		st johnstone	22/23	s1	draw
4	airdrieonians	lost		void	22/23	s1	void	void
5	ayr united	win
6	void	void
7	void	void
8	inverness caledonian thistle	draw
9	patrick thistle	draw
10	queens park	draw
11	morton	draw
12	raith rovers	draw
13	dundee united	draw
14	cove rangers	win
15	annan athletic	lost
16	edinburrh city	lost
17	kelty hearts	win
18	montrose	win
19	alloa atheletic	lost
20	aberdeen	void
21	aberdeen	lost
22	aberdeen	win
Sheet1

Cell Formulas
Range	Formula
G2:J2,G4:H4,G3	G2	=TRANSPOSE(FILTER($B$2:$B$22,$A$2:$A$22=D2,""))
Dynamic array formulas.

Amazing! Thanks a lot for the quick response and it works just as I needed!

 

[ExceLoki]

happy to help.
if the solution works for you, please consider marking it as a solution for others to find as well

 

[alansidman]

Alternative solution with Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"team"}, {{"Data", each _, type table [team=text, result=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"result", "Index"}, {"result", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "result")
in
    #"Pivoted Column"