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

[Chirag_Gandhi07]

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!

[Chirag_Gandhi07]

@KendaI thought it would work too but it only displays the software titles with one yes and one no. So for example: Tableau with emails test@test.com and test2@test.com with yes for test and no for test2 would display but if I added a third, test3@test.com that has software title Tableau with yes for Q4, then the software title Tableau doesn't display. It only displays if there are two records with yes and no not if there are more than two with yes, yes, no, or any other combination. Does that make better sense?

[Kenda]

@Chirag_Gandhi07 I see what you are saying, but I'm not sure why you are getting that output. As shown in the screenshots in my last post (copied below), there are four records for Tableau (Yes, No, Yes, unrespondent) which all appear in the output. Only the Yes and No Q4 values have the word split, as shown in records 1, 5, and 6 of the output. There are 8 records from the Input and 8 records that come out the Browse. Are you changing some of the configuration settings after downloading the workflow? Download the attached workflow and run it without making any adjustments. Are you seeing the same input and output? 

Input

Output

[Chirag_Gandhi07]

@KendaYes I am. Not sure what's going on with my workflow. Anyway, thank you so much for your help! Really appreciate it!

[Kenda]

@Chirag_Gandhi07 Is the output photo from my last post what you are looking for?

If you downloaded my very last workflow and tried running that as-is and are still not getting the output I posted as a screenshot, that would be very weird. If this is what you did and the screenshot I posted is what you're looking for in your output, try attaching your workflow here, and I can try running that to see if I get the same output as you. Please also attach a screenshot of the output you're seeing so that I can check it against mine after running.

[Chirag_Gandhi07]

@KendaI think I found the issue. Since the three options for Q4 are Yes, No, and Unrespondent, when I group by both Software Title and Q4, it only shows once per those three options. So I am looking at my original data and there are five different emails, with the same Software Title, and Yes, Yes, No, Unrespondent, and Unrespondent for Q4. When I use the summarize tool, rather than having five outputs, it only has three. One for Yes, one for No, and one for Unrespondent. Not sure why it is doing that.

[Kenda]

@Chirag_Gandhi07

If you are looking at the output of the Summarize tool, what you are describing is what you would expect there. This is the point of the GroupBy feature. This is why you use the Join tool to get back your original data. The output you’re looking for is in the final Browse tool.

[Chirag_Gandhi07]

@KendaNo I understand. Looking at the data more closely, the software titles with Yes, No, and Unrespondent for Q4 are not displaying Split for the Yes and No rows. I have no idea why but that is the issue. Thanks for your help!

[Chirag_Gandhi07]

@KendaI have attached your workflow and I added some data. For the software title Visual Basic, split is only being displayed for Yes and Unrespondent, and not for No. Also, in my real data some software titles may only have Yes for Q4 and some may have all three options, some may just have Yes and No, and some could have 5 No's. It all depends.

[Kenda · Accepted solution]

@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.

[Chirag_Gandhi07]

@KendaThank you so much!!!