I'm not sure if this is what you're looking for, but here's a mini example I
did that may help.

I put the following in A1:C4

a b a
b d c
c g b
d e e

So, C1 has "a" in it, C2 has "c" in it. "g" is in B3. I'm going to look
for those cells in column C that also appear in A and B. I'm assuming, and
this is important, that no name appears more than once in a column.

I put the following conditional format in C1:
Formula Is: =COUNTIF($A$1:$C$4,C1)2
You'd want a condition that doesn't conflict with the one you have for your
prior week and current week success. Maybe one is a color and the other is
bold.

I then copied it to C2:C4. This highlighted the b in C3.

You can easily extend this to 5 weeks and more rows.

I hope this helps.

"Gaurav" wrote:

Hi,

I will try my best to explain this problem.

We have a running report. Weeks in columns...(Jan week 1, Jan week
2.......December week 4). In these columns we have names of people who
acheived 100% target in that particular week. Now we need 3 kinds of
information from this report.

1. Who acheived 100% in the week. This is done just by pasting names in the
column
2. Who is a consecutive acheiver (Last week and current week) This is done
by highlighting names which are also there in the previous column using
conditional formatting.
3. Anyone who acheives 100% for 5 consecutive weeks. This is the problem I
am talking about here. Currently what I have done is inserted one column
after every column and in front of every name I have entered this formula
=IF(COUNTIF(last_5_weeks_columns,name,"5 consecutive weeks acheived","")

Is there a better and easier way to do this?

Thanks
Gary