Array of Array issues

[andyfleisher]

I have some team data in the form below.

	A	B
1	Name	Team
2	Name 1	Team 1
3	Name 2	Team 1
4	Name 3	Team 1
5	Name 4	Team 1
6	Name 5	Team 2
7	Name 6	Team 2
8	Name 7	Team 2
9	Name 8	Team 2
10	Name 9	Team 2
11	Name 10	Team 2
12	Name 11	Team 3
13	Name 12	Team 3
14	Name 13	Team 3
15	Name 14	Team 3
16	Name 15	Team 4
17	Name 16	Team 4
18	Name 17	Team 4
19	Name 18	Team 4
20	Name 19	Team 4
21	Name 20	Team 4

Is there a way, with a single formula to convert it to a more columnar format like below?

	D	E	F	G
1	Team 1	Team 2	Team 3	Team 4
2	Name 1	Name 5	Name 11	Name 15
3	Name 2	Name 6	Name 12	Name 16
4	Name 3	Name 7	Name 13	Name 17
5	Name 4	Name 8	Name 14	Name 18
6		Name 9		Name 19
7		Name 10		Name 20

I was initially using TRANSPOSE and UNIQUE to get the top row of team names

In cell D1: =TRANSPOSE(UNIQUE(B2:B21))

And then a FILTER function to get the names for Team 1.

In cell D2: =FILTER($A$2:$A$21,$B$2:$B$21=D1)

I then copied over the FILTER function in each column to look at each team name. Is there a way to do it all in a single function? I thought of using MAP, but that and FILTER don't seem to get along. Since each list of names per team can be a different length, I don't think I can use MAKEARRAY.

I have a current working solution but was just hoping to get better in my excel knowledge,

Thanks a lot!

 

[SunnyAlv]

try like this

=LET(un,UNIQUE(B2:B21),nm,A2:A21,tm,B2:B21,
IFERROR(VSTACK(TRANSPOSE(un),DROP(REDUCE("",un,LAMBDA(a,b,HSTACK(a,FILTER(nm,tm=b)))),,1)),""))

 

[kvsrinivasamurthy]

Try. In D1

=LET(a,A2:A21,b,B2:B21,hdr,UNIQUE(b),c,BYROW(hdr,LAMBDA(r,TEXTJOIN("_",1,FILTER(a,b=r)))),d,TEXTSPLIT(TEXTJOIN(",",0,c),"_",",",1,0,""),TRANSPOSE(HSTACK(hdr,d)))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Name	Team
2	Name 1	Team 1		Team 1	Team 2	Team 3	Team 4
3	Name 2	Team 1		Name 1	Name 5	Name 11	Name 15
4	Name 3	Team 1		Name 2	Name 6	Name 12	Name 16
5	Name 4	Team 1		Name 3	Name 7	Name 13	Name 17
6	Name 5	Team 2		Name 4	Name 8	Name 14	Name 18
7	Name 6	Team 2			Name 9		Name 19
8	Name 7	Team 2			Name 10		Name 20
9	Name 8	Team 2
10	Name 9	Team 2
11	Name 10	Team 2
12	Name 11	Team 3
13	Name 12	Team 3
14	Name 13	Team 3
15	Name 14	Team 3
16	Name 15	Team 4
17	Name 16	Team 4
18	Name 17	Team 4
19	Name 18	Team 4
20	Name 19	Team 4
21	Name 20	Team 4
22
Data

Cell Formulas
Range	Formula
D2:G8	D2	=LET(u,UNIQUE(TRIMRANGE(B2:B1000)),m,MAX(COUNTIFS(B:B,u)),DROP(REDUCE("",u,LAMBDA(x,y,HSTACK(x,VSTACK(y,EXPAND(FILTER(A2:A1000,B2:B1000=y),m,,""))))),,1))
Dynamic array formulas.

 

[kweaver]

A 2-step process:

Book11
	D	E	F	G
2	Team 1	Team 2	Team 3	Team 4
3	Name 1	Name 5	Name 11	Name 15
4	Name 2	Name 6	Name 12	Name 16
5	Name 3	Name 7	Name 13	Name 17
6	Name 4	Name 8	Name 14	Name 18
7		Name 9		Name 19
8		Name 10		Name 20
Sheet2

Cell Formulas
Range	Formula
D2:G2	D2	=TRANSPOSE(UNIQUE(B3:B22))
D3:D6,G3:G8,F3:F6,E3:E8	D3	=FILTER($A$3:$A$22,$B$3:$B$22=D2)
Dynamic array formulas.

 

[djclements]

Another couple of options, using PIVOTBY:

=LET(
   data, SORT(TRIMRANGE(A2:B1000,2,0),2),
   name, TAKE(data,,1),
   team, DROP(data,,1),
   inum, SCAN(0,team=VSTACK("",DROP(team,-1)),LAMBDA(a,v,1+a*v)),
   DROP(PIVOTBY(inum,team,name,SINGLE,0,0,,0),,1)
)

Or GROUPBY:

=LET(
   a, DROP(GROUPBY(B2:B1000,A2:A1000,HSTACK(LAMBDA(v,TEXTJOIN("|",0,"",v,"")),ROWS),0,0,,B2:B1000<>""),1),
   n, TAKE(a,,-1),
   m, MAX(n),
   v, TEXTBEFORE(TEXTAFTER(INDEX(a,,2)&REPT("|",m-n),"|",SEQUENCE(,m)),"|"),
   TRANSPOSE(HSTACK(TAKE(a,,1),v))
)

Or an array reshaping option:

=LET(
   data, SORT(FILTER(A2:B1000,B2:B1000<>""),2),
   name, TAKE(data,,1),
   team, DROP(data,,1),
   u, UNIQUE(team),
   n, MMULT(--(TOROW(team)=u),EXPAND(1,ROWS(team),,1)),
   m, MAX(n),
   r, m-n,
   v, TOCOL(IFS(r>=SEQUENCE(,MAX(r)),u),2),
   i, ROWS(v),
   w, WRAPROWS(IF(ISERROR(i),name,SORTBY(EXPAND(name,ROWS(name)+i,,""),VSTACK(team,v))),m),
   TRANSPOSE(HSTACK(u,w))
)

 

[Georgiboy]

Here is another option using MAKEARRAY:

=LET(
   n,A2:A21,
   t,B2:B21,
   u,UNIQUE(t),
   m,MAX(COUNTIF(t,u)),
   VSTACK(TOROW(u),MAKEARRAY(m,ROWS(u),LAMBDA(r,c,IFERROR(INDEX(FILTER(n,t=INDEX(u,c)),r),""))))
)

 

[andyfleisher]

A 2-step process:

Book11
	D	E	F	G
2	Team 1	Team 2	Team 3	Team 4
3	Name 1	Name 5	Name 11	Name 15
4	Name 2	Name 6	Name 12	Name 16
5	Name 3	Name 7	Name 13	Name 17
6	Name 4	Name 8	Name 14	Name 18
7		Name 9		Name 19
8		Name 10		Name 20
Sheet2

Cell Formulas
Range	Formula
D2:G2	D2	=TRANSPOSE(UNIQUE(B3:B22))
D3:D6,G3:G8,F3:F6,E3:E8	D3	=FILTER($A$3:$A$22,$B$3:$B$22=D2)
Dynamic array formulas.

Thanks.

This is what I had originally done. It worked but I knew there had to be a single cell formula to figure it out as well

 

[andyfleisher]

Thanks everyone. It's nice to see that there are so many varied solutions. I think I understand most of them, and it's fun to break down the LET formulas to see how it is all working.

 

[Fluff]

Glad we could help & thanks for the feedback.