dont know which function to use.

[Gaurav]

Hi all,

I am not sure which function to use in this scenario which could make my job
easier.

This is what i have:-

column A - agents name. column B - Team Leader's name (this is for week 1).
column C and D same data for week 2. then week 3, week 4 and week 5.

This is what I want to acheive:-

I have to check if an agent has worked under the same Team Lead for all the
weeks or he worked under different TLs. If all 5 columns (that have Team
Leaders names) match, well n good. If any one, two, three, four or even all
five of them are different, i want to highlight that row.

Bottlenecks:-

We pasted this data for all five weeks from five different reports so the
Agents names might not be alligned (even after sorting), For example - in
week 1 the first agent was X, in week 2 he resigned so when we sorted the
data, we might have different agents in the same row. quite confusing. let
me know if need to provide anymore information.

If this has an easier way, it will save a lot of time and something else as
well

Thanks

 

[Gaurav]

Guess what?

I figured it out myself. would like to share how i did it.

copied all the agents for all the five weeks in one row. then extracted
unique names. that gave me any and all agents worked in that particular
month. then used Vlookup to find their team leads for all the five weeks.
and finally used conditional format to highlight by a condition if any of
the last four weeks is not equal to the first week, turn red.

its a long way but i had no other choice. waited for pretty long for the
answer here. I would still like to know if there is an easier way.

thanks

 

[ilia]

Put all weeks in two columns. Add a new column and use it to indicate
Week 1, Week 2, Week3, etc. Select all three columns including
headers, and create a PivotTable. In this PivotTable, add rows in
this order: Lead, Agent, Week #. You can continue adding future
weeks' data, update data range, and refresh the pivot table.

Hope that helps.

 

[Gaurav]

I have 5 weeks. What do you mean by put all the weeks in 2 columns?