Removing duplicate lines based on value in one column

[MCDR929]

Hello - 

I've been struggling with this workflow for days.  Essentially, it's pulling information from a database that is constantly updated, and combining it with information logged before.  Each line of data will, at some point, receive a "Yes" or "No" in one column, but are null before being addressed.  So, when a new line enters the picture, the Y/N field is null.  The next day, someone will assign a Yes or No via a gallery app.  That updated file then joins the previous day's data, because all un-checked data must continue to appear until it is checked.  So, at input, there will be 2 identical lines except for the fact that one line will have a Yes or a No in the Y/N column.  This is the one I need to keep.  I have tried so many ways of eliminating duplicates, and all was going fine until recently. No idea where it broke down, but for the life of me I can't get duplicates removed.  The only way that almost works is to use the unique tool, but it appears to be keeping the lines without a Y/N designation.

I work with PHI, and the dataset is so massive I have no idea how to export compliant data to provide you with my workflow, so I apologize.  Hopefully someone knows of a good formula to use.  Long story short, I want to remove duplicated lines where all columns match except one, and from that one, I want to discard lines with null.

Thank you in advance!

[cmcclellan · Accepted solution]

In this case you must sort before the unique tool.  The unique tool makes the first record unique and all other records are duplicates.

Depending on the workflow it could be as simple as changing a join somewhere, or another simple step is doing a sort on the required field just before the unique.

[MCDR929]

Thank you!  I actually had this as a "shower moment" over the weekend, so I was really glad to see your response aligned with that.  Thanks so much for the quick reply!

[cmcclellan]

Awesome :)