Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return names which are repeated 5 times
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return names which are repeated 5 times
oh correction to the formula.
=IF(COUNTIF(last_5_weeks_columns,name)4,"5 consecutive weeks acheived","") "Gaurav" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return names which are repeated 5 times
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return a list of names, from a large list of repeated names. | Excel Worksheet Functions | |||
how to sum all values of a variable that's repeated multiple times | Excel Worksheet Functions | |||
Max no. of times a text repeated | Excel Discussion (Misc queries) | |||
Inserting Data Repeated Times | Excel Discussion (Misc queries) | |||
Count No. of times Dates are repeated | Excel Discussion (Misc queries) |