Hi all,

I'm work in a call center and am looking at putting some automation on the call auditing spreadsheet we use.

Consider the following as worksheets in a workbook. We use different codes (BD01,BD02 etc) to identify a type of failure on a call and 'P' to show an auditing pass.
I've got the conditional formatting sorted to show Green for P, Purple for CD** and Blue for BD** which is great, the next challenge is showing Red where the same type of code exists for that agent in the previous month (ie Scott has a BD01 in Jan and a BD06 in Feb - Both BD** failures) and Black with White writing where an agent has the same code failures over two months.

Agent (Jan 17) Call 1 Call 2 Call 3
Luke P P P
Scott P BD01 P
Mark CD01 P P


Agent (Jan 17) Call 1 Call 2 Call 3
Luke P P BD06
Scott CD01 P CD02
Mark BD03 P P

I'm stuck on keeping conditional formatting looking at the same agent rows month on month. If I was using the whole sheet I would probably use something like
Code:
=MATCH("BD??",'Jan 2017'!A2:D2,0)
for the same type of code and
Code:
=MATCH(B4,'Jan 2017'!A2:D2,0)
to check if the same code exists in the previous months call sheet.

IS there a way for the conditional formatting to check the agent name in Column A against the same agent's row in the previous month's sheet and see if there is a partial match or an exact match in any of the calls?