Comparing Comma Separated Items

Hello All,

I am stuck on a problem. I have two data sets, August and September, it has people's names on it and then the sites that they manage.

August:

John Smith

B209, B274, B408, B13276, B434

September:

John Smith

B209, B274, B408, B13276, B434, B13440, WVO, B261

I am looking to match the names and dynamically compare the two months, recognize/output which sites were added or removed in September, or if they stayed the same.

If this isn't enough info I can provide an example.

Developer Tools

Parse

Transformation

Join

Accepted answers

alexnajm

You can split to rows with a Text to Columns and then Join on them - the Join tool would then spit out what does match and what doesn't match across both sets. Workflow attached to illustrate:

Comparing Comma Separated Items.yxmd

All comments

alexnajm

You can split to rows with a Text to Columns and then Join on them - the Join tool would then spit out what does match and what doesn't match across both sets. Workflow attached to illustrate:

Comparing Comma Separated Items.yxmd

dreldrel

I think @alexnajm provides a good solution, is this fixed?

Skyline_TnL

@alexnajm & @dreldrel This is good, but I need to be able to combine them back into the data to show what was gained / lost / stayed the same. I think I could put some formula tools on each output of the join and flag them as "lost month over month" or "gained" or "no change" if all of the sites stayed the same.

Would that work? And to get them back into the same format I think I could use a Summarize tool to group by name and concat the sites?

alexnajm

Yes that should work!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703