A Formula for Splitting Results into Two Columns Based on 'WRT/PUB Ind' Criteria.

[Zee996]

Hi all,

Is there a formula that can help me in obtaining results in two separate columns based on the criteria in the "WRT/PUB Ind" column?

I have attempted to use the Filter function, but it did not yield the desired output (attached).

Is there an alternative formula that can help me achieve this, considering the criteria in the "WRT/PUB Ind" column?

	Dataset			Output
	Title	WRT/PUB Ind	Participant		Title	P	W
	ABC	P	Zee		ABC	Zee	Andrew
	ABC	W	Andrew			Helen	Kritis
	ABC	P	Helen			Joe
	ABC	W	Kritis			Josh
	ABC	P	Joe		XYZ	KIWI	Felix
	ABC	P	Josh			MACH	GARCIA
	XYZ	P	KIWI			EL ARTESANO
	XYZ	W	Felix
	XYZ	W	GARCIA		QUE	KINGCOL	VARGA
	XYZ	P	MACH			EL ARTESANO	FELIX
	XYZ	P	EL ARTESANO			NICTORI	RODRIGUEZ
	QUE	P	KINGCOL			CREW	MUNOZ
	QUE	P	EL ARTESANO			YAKAL	PEREZ
	QUE	W	VARGA
	QUE	P	NICTORI
	QUE	W	FELIX
	QUE	W	RODRIGUEZ
	QUE	W	MUNOZ
	QUE	W	PEREZ
	QUE	P	CREW
	QUE	P	YAKAL

UB Ind" column?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Title	WRT/PUB Ind	Participant		Title	P	W
2	ABC	P	Zee		ABC	Zee	Andrew
3	ABC	W	Andrew			Helen	Kritis
4	ABC	P	Helen			Joe
5	ABC	W	Kritis			Josh
6	ABC	P	Joe		XYZ	KIWI	Felix
7	ABC	P	Josh			MACH	GARCIA
8	XYZ	P	KIWI			EL ARTESANO
9	XYZ	W	Felix		QUE	KINGCOL	VARGA
10	XYZ	W	GARCIA			EL ARTESANO	FELIX
11	XYZ	P	MACH			NICTORI	RODRIGUEZ
12	XYZ	P	EL ARTESANO			CREW	MUNOZ
13	QUE	P	KINGCOL			YAKAL	PEREZ
14	QUE	P	EL ARTESANO
15	QUE	W	VARGA
16	QUE	P	NICTORI
17	QUE	W	FELIX
18	QUE	W	RODRIGUEZ
19	QUE	W	MUNOZ
20	QUE	W	PEREZ
21	QUE	P	CREW
22	QUE	P	YAKAL
23
Data

Cell Formulas
Range	Formula
E1:G13	E1	=LET(ua,UNIQUE(FILTER(A2:A100,A2:A100<>"")),ub,UNIQUE(FILTER(B2:B100,B2:B100<>"")),IFNA(REDUCE(HSTACK("Title",TOROW(ub)),ua,LAMBDA(x,y,VSTACK(x,HSTACK(y,FILTER(C2:C100,(A2:A100=y)*(B2:B100=INDEX(ub,1)),""),FILTER(C2:C100,(A2:A100=y)*(B2:B100=INDEX(ub,2)),""))))),""))
Dynamic array formulas.

 

[Zee996]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Title	WRT/PUB Ind	Participant		Title	P	W
2	ABC	P	Zee		ABC	Zee	Andrew
3	ABC	W	Andrew			Helen	Kritis
4	ABC	P	Helen			Joe
5	ABC	W	Kritis			Josh
6	ABC	P	Joe		XYZ	KIWI	Felix
7	ABC	P	Josh			MACH	GARCIA
8	XYZ	P	KIWI			EL ARTESANO
9	XYZ	W	Felix		QUE	KINGCOL	VARGA
10	XYZ	W	GARCIA			EL ARTESANO	FELIX
11	XYZ	P	MACH			NICTORI	RODRIGUEZ
12	XYZ	P	EL ARTESANO			CREW	MUNOZ
13	QUE	P	KINGCOL			YAKAL	PEREZ
14	QUE	P	EL ARTESANO
15	QUE	W	VARGA
16	QUE	P	NICTORI
17	QUE	W	FELIX
18	QUE	W	RODRIGUEZ
19	QUE	W	MUNOZ
20	QUE	W	PEREZ
21	QUE	P	CREW
22	QUE	P	YAKAL
23
Data

Cell Formulas
Range	Formula
E1:G13	E1	=LET(ua,UNIQUE(FILTER(A2:A100,A2:A100<>"")),ub,UNIQUE(FILTER(B2:B100,B2:B100<>"")),IFNA(REDUCE(HSTACK("Title",TOROW(ub)),ua,LAMBDA(x,y,VSTACK(x,HSTACK(y,FILTER(C2:C100,(A2:A100=y)*(B2:B100=INDEX(ub,1)),""),FILTER(C2:C100,(A2:A100=y)*(B2:B100=INDEX(ub,2)),""))))),""))
Dynamic array formulas.

Thank you so much for this; it works like magic.

Would it be possible to know why 'ua', 'ub', and 'y' are used here?

 

[Fluff]

They are just variables & you can rename them to pretty much anything you want.

 

[Zee996]

Thank you so much for explaining.

Thanks again for your help; it means a lot.

 

[Fluff]

You're welcome & thanks for the feedback.