Dynamic Array Help

[anthonyexcel]

Good morning,

I have been trying to learn dynamic arrays. Slowly but surely! Can someone please help me on this one? I am trying to achieve this with the data below. Thanks in advance!

Filter Breakdown to Dynamic Array.xlsm A B C D E F G H I 1 Name Place 2 Bob BAR BAR Bob 3 Mike BAS BAS Mike 4 George BRY BRY George Bob Ray 5 Bob BRY PCM Don 6 Don PCM CSA Al 7 Ray BRY HHM Penny Ant 8 Al CSA MCM Jeff 9 Penny HHM 10 Ant HHM 11 Jeff MCM 12 13 Sheet1 Cell Formulas Range Formula E2:E8 E2 =UNIQUE(B2:B11) F2:F3,F5:F6,F8,F7:G7,F4:H4 F2 =TRANSPOSE(FILTER(A2:A11,B2:B11=E2)) Dynamic array formulas.

 

[Fluff]

What's wrong with what you posted? It's the most efficient way of doing it.

 

[Fluff]

One option is

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Name	Place
2	Bob	BAR			BAR	Bob
3	Mike	BAS			BAS	Mike
4	George	BRY			BRY	George	Bob	Ray
5	Bob	BRY			PCM	Don
6	Don	PCM			CSA	Al
7	Ray	BRY			HHM	Penny	Ant
8	Al	CSA			MCM	Jeff
9	Penny	HHM
10	Ant	HHM
11	Jeff	MCM
12
Master

Cell Formulas
Range	Formula
E2:H8	E2	=LET(u,UNIQUE(FILTER(B2:B100,B2:B100<>"")),m,MAX(COUNTIFS(B:B,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(A2:A100,B2:B100=y)),,m,""))))),1))
Dynamic array formulas.

But this could get painfully slow with large amounts of data.

 

[anthonyexcel]

Fluff that is what I am looking for. I wanted to get a formula that spilled which you have provided! Thank you again!

 

[Fluff]

You're welcome & thanks for the feedback.