Raw Data
CM Name White Red Blue
Danice 1 1 1
Danice 1
Janice 1 1 1
Janice 1 1 1
Janice 1 1 1
Lyka
Melissa 1 1 1

Output
Janice Danice Lyka Mellisa
White Missing Incomplete Incomplete Completed
Red Completed Completed Incomplete Completed
Blue Missing Incomplete Incomplete Completed

Rule for the output
1. regardless how many times the name appears if theres something missing it under any color it will be tagged as Incompleted
2. if all are populated the it is set to 1
3. if all fields are missing it will be Missing