Filter out data from one column based on values from another column

Hi everyone,

I have a column (Software Title) that contains software titles and another column (Q4) that contains the values yes or no based on email addresses. I would like to filter out any software title that appears more than once and has yes and no for (Q4). Below is an example;

Email Q4 Software Title

test@test.com Yes Tableau

test1@test.com No Tableau

test2@test.com Yes Alteryx

test3@test.com No Visual Studio

So the filtered data would be:

Email Q4 Software Title

test@test.com Yes Tableau

test1@test.com No Tableau

(The filtered data is as is because Tableau shows up twice AND has both yes and no for Q4). Any help on how this can be achieved would be much appreciated. Thanks!

Workflow

Accepted answers

Kenda

@Chirag_Gandhi07 After the right Join, just add a Formula tool to the J output to create a new field with "Split" as the expression. Then output to the Union tool. The other records will be null for this new field. You can leave them that way or give them a different value in the same way you made this one "Split."

chirag_gandhi07.yxmd

Kenda

@Chirag_Gandhi07

Thank you for attaching the workflow with your data. I am finally able to see the discrepancy. The reason we were seeing different things is because your Unrespondent was capitalized and mine was lower case. This caused the Summarize to sort the Q4 column differently.

Change your Multi-Field Formula expression to this:

iif(([Q4]!=[Row+1:Q4]) && ([Row-1:Q4]!=[Q4]) && (!isempty([Row-1:Q4]) || !isempty([Row+1:Q4])) && (([Row-2:Q4]="No" || [Row-1:Q4]="No" || [Q4]="No") && ([Row+2:Q4]="Yes" || [Row+1:Q4]="Yes" || [Q4]="Yes") && ([Q4]!="Unrespondent")),"Split",null())

This should get you the output you're looking for.

All comments

Kenda

Hey @Chirag_Gandhi07!

I have attached a workflow that should get you the output you're looking for. I added one more sample row of data to show what happens when you have a duplicate software title but with the same Q4 response. The Browse on the right matches the output you provided in your post while the left Browse shows the rest of the results. Note you need the CReW Only Unique macro to use this solution.

If you don't have the CReW macros or don't want to use them, an alternative would be to use a Summarize to GroupBy and Count Software Title then Filter to only keep the counts greater than one. Then you could Join back with your original data and would get the same result as you would from the Only Unique tool. I have provided this solution in a Tool Container to the far right of the workflow.

Hope this helps!

chirag_gandhi07.yxmd

Chirag_Gandhi07

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703