conditional counting for a dynamic subset of records

Hi!.

I've got a problem I can't solve. I'll explain what I need to acheive.

I am enrolling patients for a study while they are at my clinic. The patients come to the clinic more than once during the enrollment period. Let's say that each patient can come to the clinic 5 times in the next 6 months. I don't want to bother them asking them to participate in the study if I already asked it to them in one of their previous visit. The problem is that they are 3000 and as the study go it will become impossible to check it manually.

I am trying to write an excell formula to flag the patients already contacted and enrolled every time they come to the clinic.

A B C D
Consent Duplicate Concname week
1 FALSE HamiltonRobert 1
2 y TRUE HamiltonRobert 2
3 FALSE JacksonThomas 2
4 TRUE HamiltonRobert 3
5 TRUE JacksonThomas 3

Let's make an example. Mr Hamilton came to the clinic twice and a third visit is planned for week3. Column A say that he consented to participate at his 2nd visit in week 2. The duplicate column say TRUE if Concname is a duplicate.
Meanwhile, Mr Jackson came to the clinic at week 2 but I did not speak with him, so that I want to speak with him at the next visit (week 3).

I want a formula that says that Mr Hamilton has been already contacted but says that MR jakcson came to the clinic previously but I did not contact him.

I tried several ways without success. Maybe I would need something that count the y of all the raws containing Mr Hamilton's concname using an array formula. Something that counts the y in the A column relative to Mr Hamilton record and if y count>=1 then flag for every new duplication of concname.

In SAS you accomplish this by a do end loop:

if concname=HamiltonRobert then do;
<operation>;
end;

I would like to have this kind of result

A B C D E
Consent Duplicate Concname week Flag
1 FALSE HamiltonRobert 1
2 y TRUE HamiltonRobert 2 !
3 FALSE JacksonThomas 2

4 TRUE HamiltonRobert 3 !
5 Y TRUE JacksonThomas 3
4 TRUE HamiltonRobert 4 !
5 TRUE JacksonThomas 4 !

Hi!.

I've got a problem I can't solve. I'll explain what I need to acheive.

I am enrolling patients for a study while they are at my clinic. The patients come to the clinic more than once during the enrollment period. Let's say that each patient can come to the clinic 5 times in the next 6 months. I don't want to bother them asking them to participate in the study if I already asked it to them in one of their previous visit. The problem is that they are 3000 and as the study go it will become impossible to check it manually.

I am trying to write an excell formula to flag the patients already contacted and enrolled every time they come to the clinic.

A B C D
Consent Duplicate Concname week
1 FALSE HamiltonRobert 1
2 y TRUE HamiltonRobert 2
3 FALSE JacksonThomas 2
4 TRUE HamiltonRobert 3
5 TRUE JacksonThomas 3

Let's make an example. Mr Hamilton came to the clinic twice and a third visit is planned for week3. Column A say that he consented to participate at his 2nd visit in week 2. The duplicate column say TRUE if Concname is a duplicate.
Meanwhile, Mr Jackson came to the clinic at week 2 but I did not speak with him, so that I want to speak with him at the next visit (week 3).

I want a formula that says that Mr Hamilton has been already contacted but says that MR jakcson came to the clinic previously but I did not contact him.

I tried several ways without success. Maybe I would need something that count the y of all the raws containing Mr Hamilton's concname using an array formula. Something that counts the y in the A column relative to Mr Hamilton record and if y count>=1 then flag for every new duplication of concname.

In SAS you accomplish this by a do end loop:

if concname=HamiltonRobert then do;
<operation>;
end;

I would like to have this kind of result

A B C D E
Consent Duplicate Concname week Flag
1 FALSE HamiltonRobert 1
2 y TRUE HamiltonRobert 2 !
3 FALSE JacksonThomas 2

4 TRUE HamiltonRobert 3 !
5 Y TRUE JacksonThomas 3
4 TRUE HamiltonRobert 4 !
5 TRUE JacksonThomas 4 !