Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count occurances
Hi,
We get a list of people every week. This list is random...just the names of people. Now they are entered in a spreadsheet for the record. For example A1 = Week 1 and then the name list A2 downwards. B1 = Week2 and then the name list B2 downwards. Now few of the names might get repeated in different weeks but the order is never same. This is a running sheet so we keep on adding lists as they come in. What I am trying to do is that on a separate sheet I need to write headings 2,3,4,5 in Row1 and then have names of the people who have been in the lists for 2,3,4,5 CONSECUTIVE weeks. If Sam was there in Week2, Week3 and Week4, his name should be under 3 because he was there for 3 consecutive weeks. However, if it was Week2, Week3 and Week5, I dont want that name under 3...in this case it will be under 2. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count occurances
Assumptions:
A1:J1 contains Week 1, Week 2, Week 3, etc. A2:J10 contains the data/names L2 contains the first name, L3 contains the second name, L4 contains the third name, etc. M1:P1 contains 2, 3, 4, and 5 Formula: M2, confirmed with CONTROL+SHIFT+ENTER, copied across and down: =IF(MAX(FREQUENCY(IF(MMULT(TRANSPOSE(ROW($A$2:$J$1 0)^0),($A$2:$J$10=$L2)+ 0),COLUMN($A$2:$J$10)),IF(MMULT(TRANSPOSE(ROW($A$2 :$J$10)^0),($A$2:$J$10= $L2)+0)=0,COLUMN($A$2:$J$10))))=M$1,$L2,"") Adjust the references accordingly. Hope this helps! In article , "Gaurav" wrote: Hi, We get a list of people every week. This list is random...just the names of people. Now they are entered in a spreadsheet for the record. For example A1 = Week 1 and then the name list A2 downwards. B1 = Week2 and then the name list B2 downwards. Now few of the names might get repeated in different weeks but the order is never same. This is a running sheet so we keep on adding lists as they come in. What I am trying to do is that on a separate sheet I need to write headings 2,3,4,5 in Row1 and then have names of the people who have been in the lists for 2,3,4,5 CONSECUTIVE weeks. If Sam was there in Week2, Week3 and Week4, his name should be under 3 because he was there for 3 consecutive weeks. However, if it was Week2, Week3 and Week5, I dont want that name under 3...in this case it will be under 2. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count of different occurances | Excel Discussion (Misc queries) | |||
Count Occurances of a txt string | Excel Discussion (Misc queries) | |||
count occurances | Excel Discussion (Misc queries) | |||
count matching occurances | Excel Worksheet Functions | |||
Count occurances Problem | Excel Worksheet Functions |