How to combine IF and FILTER formulas to copy text from one column to another

[steviemb]

I am coding qualitative data from call center agent notes, and I'm trying to adapt another researcher's Excel method. I've adapted it a bit for my own purposes, and I just can't seem to get this last piece. I'm using Excel for Mac version 16.81

So, I have all my agent notes in Column A. In Column B, I have all the relevant tags for that note in a comma delineated list. I've created a unique list from all my tags (n=10) and turned them into headers in new columns. To organize the notes by tag, I want to copy the notes in Column A into Columns X-Z based on whether or not that note has a tag in Column B that matches the header of Column X-Z (see table below for simplified visualization).

Notes	Tags		X	Y	Z
1 (note text)	x, y		1	1

This is the other researcher's code I'm trying to adapt: =if(X1<>"",filter($A:$A, $B:$B=X1,"")

I cannot get this code to work for me- partially because this researcher only has 1 tag in their Column B, whereas I have multiple in a comma delineated list. I've been successful with a code that searches text and moves row by row, but it leaves me with blank cells if the Column A note in that row doesn't have a tag corresponding to Column X-Z.

=IF(ISNUMBER(SEARCH(X$1,$B2)),$A2,"")

Visualization of what this returns

Notes	Tags		X	Y	Z
1	x, y		1	1
2	x, z		2		2
3	z				3

But what I really want is a way to reference the entire Column A when I populate Column X so that there aren't empty cells. I've tried changing my formula to

=IF(ISNUMBER(SEARCH(X$1,$B:$B)),$A:$A,"")

but that just returns a Spillover error. How can I change my formula to get this

Notes	Tags		X	Y	Z
1	x, y		1	1	2
2	x, z		2		3
3	z

 

[SunnyAlv]

=IF(ISNUMBER(SEARCH(X$1,$B:$B)),$A:$A,"")

Try

=LET(tags,IF(ISNUMBER(SEARCH(X$2,$B:$B)),$A:$A,""),FILTER(tags,tags<>""))

 

[Fluff]

Another option

=FILTER($A$2:$A$100,ISNUMBER(SEARCH(X$1,$B$2:$B$100)),"")

With any array formula it's best to avoid whole column references

 

[steviemb]

Another option

=FILTER($A$2:$A$100,ISNUMBER(SEARCH(X$1,$B$2:$B$100)),"")

With any array formula it's best to avoid whole column references

that worked brilliantly, thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.