Getting a distinct count based on distinct criteria if a condition is met

Totally, that's why I suggested unioning back the records from the 'D' output of the unique tool. That way the unique records are counted and the duplicates are not included in your count but still end up in your dataset.

All comments

Here's my pass at this. First I filtered to successful records, then split the forms to individual rows rather than being concatenated (i.e. 'form1, form 2' would become two rows), then I removed duplicates and summarized the data to get the count of 8.

New Workflow14.yxmd

Thanks, this got to the right number! But I would like to append the individual count to the original table by ID (alhough the IDs are duplicated for some). Or just the one total on the first line of the table. Right now if I try to append fields, it attaches the total to every line.

Thanks, but the output is double counting some of the forms. For example, 123A is showing 2 for both rows instead of just one row, so when you add up the total, it will be more than expected. Basically the count should only append to the first unique locator.

Hi @richleeb2, sorry misinterpreted your note "although the IDs are duplicated for some" as it being expected behavior.

Try this, I removed dupes, joined, and then unioned the dupes back. This should be enough for you to either get the desired result or modify a bit to get what you need.

New Workflow14.yxmd

This worked, thank you!!

@Luke_C

Hi Luke, I was wondering if there was an alternative way of presenting the data. I added a Text to Columns tool to the end because I want a count of each distinct form by ID (but no duplicates). So for example:

123A, successful, form1 = Count is 1

123A , successful, form2 = Count is 1

123A, successful, form1 = Count is 0 because you already counted form1 above

Any help would be appreciated!

New Workflow14.yxmd

Hope you had a good weekend. For your question, this is already sort of being done in the initial part of the workflow. I think by tweaking this area or the summarize tool you could get what you want. The first text to columns tool splits out the rows that have multiple forms listed in one record. Then the remove duplicates tool removes duplicates (i.e. 123A form 1) prior to counting by ID. If you update the summarize tool to group by ID and Form, then union the 'D' anchor of the unique tool, you should be able to get something like what you describe above.

Thanks Luke, but I don't want to delete any rows because my actual data has other columns with data. But I'll play around with it some more.