Finding frequency

[Guest]

B2:R2 has
1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb 9-Feb 10-Feb Dick Grace Harry Maxi Renny Tim Tom

A3:R3 has
Test1 Tom Dick Tom Maxi Tom Dick Dick Harry Tim Tom 0 N/A N/A N/A N/A N/A 1

A4:R4 has
Test2 Dick Maxi Maxi Grace Renny Renny Tom Harry Dick Tom 7 N/A N/A 0 0 N/A 0


I am looking out formula for range L3:R4

There are two tests happening for 7 participants.
The one who tops the test, his name is displayed below that date. I want to
check the minimum difference for the toppers.

Eg. Dick topped the test1 thrice (on 2nd 6th and 7th) Out of these three, he
topped on two consecutive days, hence the difference is 0. This should be
displayed in cell L3

Similarly, Tom topped 4 times but on two occassions, he topped on the
alternate days hence the value for him is 1.

Maxi

 

[Guest]

I think the entire design in messed up. Let me put this question again.

[Data in B2:R2]
1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb 9-Feb 10-Feb Dick Grace
Harry Maxi Renny Tim Tom

[Data in A3:R3]
Test1 Tom Dick Tom Maxi Tom Dick Dick Harry Tim Tom 0 N/A N/A N/A N/A N/A 1

[Data in A4:R4]
Test2 Dick Maxi Maxi Grace Renny Renny Tom Harry Dick Tom 7 N/A N/A 0 0 N/A 0

I am looking out formula for range L3:R4

There are two tests happening for 7 participants. The one who tops the test,
his name is displayed below that date. I want to check the minimum difference
for the toppers.

Eg. Dick topped the test1 thrice (on 2nd 6th and 7th) Out of these three, he
topped on two consecutive days, hence the difference is 0. This should be
displayed in cell L3

Similarly, Tom topped 4 times but on two occassions, he topped on the
alternate days hence the value for him is 1.

Maxi

 

[BobT]

My solution is much more complicated than putting formulas
in the L3:R4 range but maybe someone can improve on it.

A6> "Dick"
A7> =A6

B6> =IF(B$3=$A6,COLUMN(B$3),"")
B7> =IF(B$4=$A7,COLUMN(B$4),"")
Copy B6:B7 across B6:K7

C8>=IF(C6="","",IF(COUNTIF($B6:C6,">1")>1,C6-MAX
($B6:B6),""))
Copy C8 across C8:K8

A8> =IF(MIN(C8:K8)=0,"N/A",MIN(C8:K8)-1)
Copy to A9

Now you can copy A6:K9 down to A11:K14
Change A11 to "Grace"

Repeat for each "contestent".

L3> =INDIRECT("A"&MATCH(L$2,$A:$A,0)+2)
L4> =INDIRECT("A"&MATCH(L$2,$A:$A,0)+3)

Copy L3:L4 across L3:R4

Note: in L3 and L4 you may prefer an index function rather
than indirect and a row() call rather than +2 and +3

That was fun, hard to believe no one else replied while I
worked it out.
Bob

 

[BobT]

By the way, as this thread is already a day old and has
replies, the experts around here might not have another
look.

If you don't see any other replies for a day or two, you
might want to post my reply as a new thread, asking for
someone to make it better

 

[HighTide]

Where it says
"Copy C8 across C8:K8" below, it should be
Copy C8 across C8:K9

My solution is much more complicated than putting formulas
in the L3:R4 range but maybe someone can improve on it.

A6> "Dick"
A7> =A6

B6> =IF(B$3=$A6,COLUMN(B$3),"")
B7> =IF(B$4=$A7,COLUMN(B$4),"")
Copy B6:B7 across B6:K7

C8>=IF(C6="","",IF(COUNTIF($B6:C6,">1")>1,C6-MAX
($B6:B6),""))
Copy C8 across C8:K8

A8> =IF(MIN(C8:K8)=0,"N/A",MIN(C8:K8)-1)
Copy to A9

Now you can copy A6:K9 down to A11:K14
Change A11 to "Grace"

Repeat for each "contestent".

L3> =INDIRECT("A"&MATCH(L$2,$A:$A,0)+2)
L4> =INDIRECT("A"&MATCH(L$2,$A:$A,0)+3)

Copy L3:L4 across L3:R4

Note: in L3 and L4 you may prefer an index function rather
than indirect and a row() call rather than +2 and +3

That was fun, hard to believe no one else replied while I
worked it out.
Bob

-----Original Message-----
I think the entire design in messed up. Let me put this question again.

[Data in B2:R2]
1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb 9-Feb 10- Feb Dick Grace
Harry Maxi Renny Tim Tom

[Data in A3:R3]
Test1 Tom Dick Tom Maxi Tom Dick Dick Harry Tim Tom 0 N/A N/A N/A N/A N/A 1

[Data in A4:R4]
Test2 Dick Maxi Maxi Grace Renny Renny Tom Harry Dick Tom 7 N/A N/A 0 0 N/A 0

I am looking out formula for range L3:R4

There are two tests happening for 7 participants. The one who tops the test,
his name is displayed below that date. I want to check the minimum difference
for the toppers.

Eg. Dick topped the test1 thrice (on 2nd 6th and 7th) Out of these three, he
topped on two consecutive days, hence the difference is 0. This should be
displayed in cell L3

Similarly, Tom topped 4 times but on two occassions, he topped on the
alternate days hence the value for him is 1.

Maxi
.