Excel 方法从导出的培训系统数据中标记未完成的课程

Question

所以我有一个数据导出

我正在尝试找到一种方法来标记用户是否未完成培训课程。所以我试图实现如下输出

我一直在阅读并尝试一些类似的东西COUNTIF，INDEX但我就是无法解决。

图像的 CSV：

First Name,Last Name,User's Email,Team,Course name,Completion status,Progress (% complete),
Name 1,Lname 1,Name 1.Lname [email protected],HR,basic first aid,Complete via rpl,100,
Name 2,Lname 2,Name 2.Lname [email protected],Technology,basic first aid,Completed,100,
Name 3,Lname 3,Name 3.Lname [email protected],Technology,basic first aid,Not started,0,
Name 4,Lname 4,Name 4.Lname [email protected],Technology,basic first aid,Completed,100,
Name 5,Lname 5,Name 5.Lname [email protected],Technology,basic first aid,In progress,0,
Name 6,Lname 6,Name 6.Lname [email protected],Network,basic first aid,Completed,100,
Name 7,Lname 7,Name 7.Lname [email protected],Technology,basic first aid,Completed,100,
Name 8,Lname 8,Name 8.Lname [email protected],Marketing,basic first aid,Completed,100,
Name 9,Lname 9,Name 9.Lname [email protected],Technology,basic first aid,Not started,0,
Name 6,Lname 6,Name 6.Lname [email protected],Network,becoming a manager,Completed,100,
,,,,,,,
,,,,,,,
,,,,,,,
First Name,Last Name,User's Email,Basic First Aid,becoming a manager,,,
Name 1,Lname 1,Name 1.Lname [email protected],TRUE,FALSE,,,
Name 2,Lname 2,Name 2.Lname [email protected],TRUE,FALSE,,,
Name 3,Lname 3,Name 3.Lname [email protected],FALSE,FALSE,,,
Name 4,Lname 4,Name 4.Lname [email protected],TRUE,FALSE,,,
Name 5,Lname 5,Name 5.Lname [email protected],FALSE,FALSE,,,
Name 6,Lname 6,Name 6.Lname [email protected],TRUE,TRUE,,,
Name 7,Lname 7,Name 7.Lname [email protected],TRUE,FALSE,,,
Name 8,Lname 8,Name 8.Lname [email protected],TRUE,FALSE,,,
Name 9,Lname 9,Name 9.Lname [email protected],FALSE,FALSE,,,