Data Cleaning - String to Columns But Only Include Certain Values

[Arktics]

Hi team,

Hoping somebody could help me out with this!

Image for context, I have extracted data from an example sports event as exported from the system being used. It returns a string of Events (column F) e.g.:

TEAM2 Offence,TEAM1 Defence,TEAM2 Pass to CF,TEAM1 Pass to CF Conceded,TEAM2 Shot,TEAM1 GK Save,TEAM2 SoT,TEAM1 GK Save Left,TEAM2 Bounce Shot,TEAM2 Shot Inside 5m,TEAM2 Defence,TEAM1 Offence,Q1

I've used text-to-column to break down each of these into individual events which is okay - however this results in duplicate results as when a Team Offence event is recorded, it automatically assigns a Team Defence event as well. What I need is a way to draw out this string but to only include events that include the value in column E, i.e. TEAM1.

Appreciate if anyone has any advice on how to achieve this!

Thankyou.

 

[Peter_SSs]

Welcome to the MrExcel board!

I wasn't entirely sure, but is this what you mean?

23 06 21.xlsm
	E	F	G	H	I	J	K	L
2	TEAM1	TEAM2 Offence,TEAM1 Defence,TEAM2 Pass to CF,TEAM1 Pass to CF Conceded,TEAM2 Shot,TEAM1 GK Save,TEAM2 SoT,TEAM1 GK Save Left,TEAM2 Bounce Shot,TEAM2 Shot Inside 5m,TEAM2 Defence,TEAM1 Offence,Q1	TEAM1 Defence	TEAM1 Pass to CF Conceded	TEAM1 GK Save	TEAM1 GK Save Left	TEAM1 Offence
Team Data

Cell Formulas
Range	Formula
G2:K2	G2	=LET(ts,TEXTSPLIT(F2,","),FILTER(ts,LEFT(ts,LEN(E2)+1)=E2&" ",""))
Dynamic array formulas.