[Request] Identifying Amount of Matches in a Set of Record Using a List

[Rice]

Hi,

Scenario: I have a list of Actions that are assigned to various users within the organization. We use those Actions to identify where the concentration of work is distributed. It has been requested by a specific organization to run the Action Owners against their list of employees. The result(s) would allow us to verify if people are properly assigned and Actions that need review.

I have a list of 50 employees and need to see if there is any mention of any employee in a field list of another set of data.

Using alphabets for easy reference:

Set A: [A,B,C,D,E,F...] (Call it 26 unique names)

Set B:[AB,A,1,2,4,6,Z,E,F]

The result would help me identify that AB, A, Z, E, F have matches to Set A. Let's assume the numeric numbers are other employees in other organizations.

I have found one solution similar to this but isn't the most sexy, scale-able, easy to comprehend for non-Alteryx users and that is: having my first column be the list I want to check for matches then appending to the first row the list of employees I'm checking - doing a multi-field formula which would count for any hits - summing and then filtering down.

Suggestions on other methods?

Thank you in advance!

[Claje · Accepted solution]

Hi,

I'm not sure exactly what you're doing with the data after you find matches, so the attached does not do any final summarization, but I think it should get you close to what you need.

The added benefit is that it should be much faster on large datasets than doing a full Append of all employees to ALL Actions.

I tried to include some notes on why I included each step in this example.  Hope it helps!

[MarqueeCrew · Accepted solution]

@Claje,

What an impressive workflow you built.  I have simplified the process with this:

Since @Rice only has 50 employees, let's just append each set A record to every set B record (* 26 in this workflow).  Now filter for where B contains A and cleanup with a select and UNIQUE (AB matches 2 names).  

Cheers,

Mark

[Rice]

Thank you very much - the picture helped me scan quite easily to apply it to my data set.

The issue I had was overcoming that there aren't m(any) tools that allow for contains and your solution was able to stop me in the middle of a transposing and cross-tab nightmare.

[Claje]

Nice workflow name @MarqueeCrew ;)

[MarqueeCrew]

Thanks for the feedback and for asking your question. Please do note that Jessie's workflow also solves the challenge. While poking a little fun at the #1 Certified Alteryx guru, I do encourage lots of alternative solutions and hope that you'll mark his as a solve too ( along with any future good suggestions).

Cheers,

Mark

[MarqueeCrew]

Ps: I have a macro that searches for a list of terms.

https://gallery.alteryx.com/#!app/Contains-In-List/5a5903fda18e9e2508e5ae0d