On Sat, 5 Dec 2009 23:30:01 -0800, JRD
wrote:

I need excel 2007 to perform a minor miracle!

Here's the problem:


A B C D
E
1 Number Procedure Date Procedure repeated? Days between
2 procs
2 123 RCA; LAD 01/01/08 Yes
521
3 213 LAD 02/01/08 Yes
631
4 314 RCA; Cx 02/02/08 No
637
5 489 RCA; Cx 03/04/08 Yes
535
6 213 Cx 02/01/09 No
302
7 512 LMS; graft 04/04/09 No
210
8 123 RCA; Cx 05/06/09 No
148
9 489 Cx 20/09/09 Yes
33
10 213 LAD 24/09/09 No
37
11 892 RCA; Cx 28/09/09 No
33
12 198 LMS 15/10/09 No
16
13 489 Cx 23/10/09 No
8


Columns A, B and C contain raw data. Column A is a number relating to a
person. Column B is a procedure that has been carried out (note some patients
have more than one procedure seperated by a semicolon e.g. row 2 had RCA and
LAD) and column C is the date of the procedure.

In column D, I need excel 2007 to work out whether the procedure that was
carried out was later repeated (i.e. on a date following the date of the
procedure in this row). I have filled in the answers that should come out in
column D. For example, in row 2 the answer is Yes, as in row 8 the same
person (123) had a repeat "RCA" procedure on a later date in row 8

In column E, I need to calculate the number of days between the dates of
repeated procedures. If there hasn't been a repeated procedure then I want
the number of days between a pre-specified date (in this case 31/10/09) and
the procedure date. Therefore, in row 2 it is the difference in days between
C2 and C8. In row 4 it is difference between 31/10/09 and C4.

If a procedure has been repeated on a person more than twice, I just need
the number of days between the 3rd and 2nd procedure (e.g. person 489 who has
had 3 "Cx" procedures.

Can this be done anyone?

Many, many thanks,


A complicating factor is that you allow more than one procedure on the
same row.
In you example you have RCA and LAD for number 123 on January 1 2008.
On June 5 2009 you have RCA for number 123, but what about LAD?
Please specify how to handle the multiple procedures.

If you can rearrange your data to have just one single procedure on
each row, you may try the following:

In cell D2 put:

=IF(SUMPRODUCT((A3:A$1000=A2)*(B3:B$1000=B2))0,"Y es","No")

In cell E2 put:

=IF(D2="No","31/10/09",INDEX(C3:C$1000,MIN(IF((A3:A$1000=A2)*(B3:B$10 00=B2),(ROW(A3:A$1000)-ROW(A2))))))-C2

Note: the formula in cell E2 is an array formula that must be
confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

Change the 1000 i all places to fit the size of your data.

Copy cells D2 and E2 down as far as you have data in columns A to C.

Hope this helps / Lars-Åke