Method to identify a field that meets two separate criteria over multiple rows

[James_Simpson_125]

Hello, 

The current issue I have is trying to identify when multiple orders went out to the same customer on the same week but by different methods. 

What I currently have done is create a concatenation that is [customer] + [customer location] + [year-week] so it would read as:  Apple-CityStateZIP-202203

In the example I'm looking to solve, there are multiple rows that would contain the 'Apple-CityStateZIP-202203' but for a different field/column- [Method of Transportation]- it would then contain one of a list of options, such as Third Party, Second Party or First Party. 

example:

  Concatenation                            Method of Transportation       Order Number        

Apple-CityStateZIP-202203                    First Party                              1

Apple-CityStateZIP-202203                    Third Party                             2

Apple-CityStateZIP-202204                    First Party                              3

Apple-CityStateZIP-202204                    First Party                              4

How do I create a field/formula that would identify Concatenations that contain both First Party and Third Party, but in the example of the 'Apple-CityStateZIP-202204' where both were first party, to ignore it/provide an answer that would read something along the lines of "not of concern/contracted work". 

I am open to other methods as well, this logic is what I used in excel prior to having Alteryx, as I am currently re-creating the work. 

[Felipe_Ribeir0 · Accepted solution]

Hi @James_Simpson_125 

One way of doing this, just filter as you wish

[ChrisTX · Accepted solution]

The attached workflow should point you in the right direction.

Chris