Compare if data matches in different columns (no particular order)

[aiste_griffiths]

Hi guys, 

I am in need of your assistance and brains please :)

After blending and joining the data I am at the stage in my workflow where I have two columns with data separated by commas that I need to compare: I need to check if data in Column "Typology Code" appears in Column  "Key Typology" in no particular order.

As an output I would need to know how many times data from column C matched data on Column D.

raw data:

staff name	app ID	Typology Code	Key Typology
Josh	1	T12	T12,T17,T18,T21,T22,T4,T7,T8
Josh	2	T21,T22	T12,T17,T18,T21,T22,T4,T7,T8
Josh	3	T12	T12,T17,T18,T21,T22,T4,T7,T8
Josh	4	T21,T21,T23,T22,T12,T7,T8	T12,T17,T18,T21,T22,T4,T7,T8
Amy	5	T7	T12,T17,T18,T19,T2,T20,T4,T6,T
Amy	6	T17,T18	T12,T17,T18,T19,T2,T20,T4,T6,T
Amy	7	T21,T21,T23,T22,T24	T12,T17,T18,T19,T2,T20,T4,T6,T
Amy	8	T7,T10,T4	T12,T17,T18,T19,T2,T20,T4,T6,T
Tom	9	T21,T21,T22,T12,T7	T12,T21,T22,T24,T4,T6,T7,T8
Tom	10	T12,T7,T8	T12,T21,T22,T24,T4,T6,T7,T8
Tom	11	T12,T7	T12,T21,T22,T24,T4,T6,T7,T8
Tom	12	T7,T24	T12,T21,T22,T24,T4,T6,T7,T8

Desired output for Josh (and for other staff):

	T12	T17	T18	T21	T22	T4	T7	T8
Josh	3	0	0	3	2	0	1	1

Could you please guide me to the right direction how to work towards such output?

Thanks so much, Aiste x

[CoG · Accepted solution]

The Text to Columns Tool is the MVT (Most Valuable Tool) in this case, allowing you to check each code individually, aggregating at the end to calculate the total counts. I'm not sure exactly what your output is supposed to look like when all [staff name]'s are considered, so I just left them all together where null implies a code was not in [Key Topology] at all.

Hope this helps and Happy Solving!

[aiste_griffiths]

Hi @CoG 

I don't know what to say - this is exactly what I needed!

Thanks so much, love this community 💖

[ChrisTX]

Try the attached workflow

 

Chris