Match substrings within a cell that are not in order, or reorganize substrings in a cell

Question

I'm working with a finite, known set of individual data strings similar to this:

AAAAAA AABBBCCCCCDDD DD

The strings will come in groups into a single cell and can look like the below:

KeyString 1String 2Match / No MatchDuplicate StringJohnsonAAA; AAA AA; BBB; CCCCC; DDD DDAAA; AAA AA; BBB; CCCCC; DDD DDMatchNoBrownDDD DD; AAA AA; BBBBBB; AAA AA; DDD DDMatchNoSmithAAA; CCCCC; DDD DDAAA; DDD DDNo matchNoJonesCCCCCCCCCC; AAANo matchNoTopekaCCCCC; AAA AA; DDD DDAAA; AAA AA; BBB; CCCCC; DDD DDNo matchNoBobBBB; CCCCC; BBBBBB; CCCCCNo matchYes

The strings always come separated with a semi colon and space, and the last string will not have a semi colon.

I would like to know a way to either match strings if they are the same but out of order as with Brown, or reorganize the strings to a particular order (say alphabetically) so that Brown's String 1 & String 2 would match using a Join tool.

I'd also like to ID whether a cell has a duplicate string in it as with Bob.

Any ideas?

jmgross72 · Accepted Answer

Hi @Rob48,

I came up with a workflow that does what you are asking.

Hope this helps! @me if you have any questions :)

flying008 · Accepted Answer

Hi, @Rob48

FYI.

Spoiler

IIF(REGEX_CountMatches(',' + [String_1], '(,[^,]+)\1,') || REGEX_CountMatches(',' + [String_2], '(,[^,]+)\1,'), 'Y', Null())

Output:

KeyTile_NumString_1String_2MatchDuplicateJohnson1AAA,AAA AA,BBB,CCCCC,DDD DDAAA,AAA AA,BBB,CCCCC,DDD DDY Brown2AAA AA,BBB,DDD DDAAA AA,BBB,DDD DDY Smith3AAA,CCCCC,DDD DDAAA,DDD DD  Jones4CCCCCAAA,CCCCC  Topeka5AAA AA,CCCCC,DDD DDAAA,AAA AA,BBB,CCCCC,DDD DD  Bob6BBB,BBB,CCCCCBBB,CCCCC Y

jrlindem · Accepted Answer

@Rob48 - Not to take anything away from @flying008 who's answer works just fine, I was curious enough about your ask and used it to practice.  I ended up with a solution that does not require Regex, if that's more your speed.  Note that Regex certainly cuts down on the number of tools though!

Here's another option to what's already been provided:

@jmgross72's works great too, it just uses the Data Cleanse Pro tool that not everyone may have access to, depending on their version.

Hope this is helpful, -Jay