Ensuring unique identifier has only one row of data

[ccroston]

Hello! Hoping to get some assistance on this matter. I have two workflows:

Workflow 1 conducts 20 audits on a report of employee data called "Roster Source". For each audit I do, I am writing the result of the "pass" (0) or "fail" (1) of the check at the end of the last column of the employee data report, Select just the "fail" (1) condition, and output to an Excel file sheet/tab. I repeat this 20 times, resulting in an excel file with 20 sheets/tabs of "fail" conditions for different checks

Workflow 2 takes the output Excel file that Workflow 1 created and puts it in a different format. I take the 20 separate tabs, Select to remove all data except the unique identifier (Employee ID) and the result of Workflow 1's audit (pass/fail or 0/1), and Union them together. I Join with the Roster Source to have the result of each audit appear at the end of the 100 columns of employee data, do some data cleanup, add a "Sum of Flags" column with Formula tool, and then output. 

However, when an employee has multiple flags in different audits, it results in multiple rows of data, and the "Sum of Flags" not working properly.

How do I make it so each employee only has one row in the data set, and the "Sum of Flags" works properly? 

[apathetichell]

transpose your 5 audit columns. use summarize tool to take max. If max is 1 - audit is yes. if max is 0 - audit is no?

[apathetichell]

and don't use a datacleanse (use a purpose built multi-field formula instead) if performance is an issue.

[ccroston]

Where would I insert the transpose?

[ccroston]

I was able to create a proper "Sum of Flags" column using Transpose. However I still have results like this. How can I make it so all the 1s appear on one line?

[apathetichell]

did you cross tab?

these are different datapoints so they are not one line. you could either try summarize

group by name/sum_of_flags

max (all of your audit categories)

or - in the same section where you do some of flags - you could have concatenated data on your audit flags to create a matrix of which fields is creating the flag.

[ccroston]

Thanks for your time. I was able to figure it out by referencing this solution - had the visuals I needed to fully understand what you were saying.