Count occurances

[Gaurav]

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?

 

[Domenic]

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.

M11 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$10)^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!