Complex function needed?

[JRD]

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,

 

[Lars-Åke Aspelin]

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,"Yes","No")

In cell E2 put:

=IF(D2="No","31/10/09",INDEX(C3:C$1000,MIN(IF((A3:A$1000=A2)*(B3:B$1000=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

 

[JRD]

Thank you Lars

The procedures (RCA, LAD, Cx, LMS, Graft) are actually blood vessels.
Therefore person 123 had blood vessels RCA and LAD treated on January 1 2008
and RCA and Cx treated on 5th June 2009. I need excel to check whether either
the RCA or the LAD has been re-treated following the initial procedure on Jan
1 2008. In this example I want excel to look down columns A and B and see
whether the same person (123) has had a repeat procedure where either the RCA
or the LAD has been retreated. It will need to do this for each row (each row
representing a single procedure, sometimes on more than one vessel), but only
checking for repeat procedures on the same vessel in the same person for
dates ahead of the date for the procedure it is checking.

This is complicated, I know, but I've tried my best to explain!

Thanks

John

 

[Lars-Åke Aspelin]

So in this situation

123 RCA; LAD 01/01/08 Yes xx
....
....
123 LAD 11/01/08 No
....
123 RCA 21/01/08 No

do you want xx to be 10 or 20?

If you split the "multiple vessels" into separate lines with just one
vessle per line, but with the same date, you would get the following
by applying the proposed formulas.

123 RCA 01/01/08 Yes 10
123 LAD 01/01/08 Yes 20
....
....
123 RCA 11/01/08 No
....
123 LAD 21/01/08 No

But maybe it is not possible for you to control the format of the
input like this.

Is there an upper limit to the number of vessels on one single line?
A formula to take care of "multiple vessel input" on one line I guess
would be rather complicated.

Alternative ways forward would be to use a helper table where the
"multiple vessel format" is translated to "single vessel format" or
use a macro, rather than worksheet functions, to fill the "Procedure
repeated" and "Days betweeen procedures" columns.

Hope this helps / Lars-Åke

 

[JRD]

In the situation below xx would be 10

You are right, I do not think it is possible to format the worksheet with
different vessels on different lines. Unless you know of an automated way to
get excel to reformat the worksheet?

The upper limit is 6 vessels

Could you help me with a macro if this is the only way?

Many thanks

John

 

[Lars-Åke Aspelin]

Here is a bunch of formulas that make use of some helper columns.
Assuming your input data is in columns A to C starting on row 2 (after
a possible header row) and that you want your output in columns D and
E.
Also assuming that you have semicolons to separate multiple
procedures on a row, but no semicolon in the procedure names and no
semicolon before the first or after the last procedure.

You may try the following formulas:
(Note: Some of the formulas are array formulas that should be
confirmed with CTRL+SHIFT+ENTER rather than just ENTER)

In cell F1 you just put a 0.

In cell F2 you put this: (note that this is an array formula)
=F1+SUM(--NOT(ISERROR(FIND(";",MID(B2,ROW(A$1:A$1000),1)))))+1

In cell J2 you put this:
=INDEX(A$2:A$1000,MATCH(ROW()-2,F$1:F$1000,1))

In cell L2 you put this:
=INDEX(C$2:C$1000,MATCH(ROW()-2,F$1:F$1000,1))

In cell G2 you put this:
=SUMPRODUCT((J$1:J1=J2)*(L$1:L1=L2))

In cell I2 you put this: (note that this is an array formula)
=SMALL(ROW(A$1:A$1000)*(IFERROR(FIND(";",MID(INDEX(B$2:B$1000&";",MATCH(ROW()-2,F$1:F$1000,1)),ROW(A$1:A$1000),1)),999)),G2+1)

In cell H2 you put the following:
=IF(G2=0,1,I1+1)

In cell K2 you put the following: (note that this is an array formula)
=TRIM(MID(INDEX(B$2:B$1000&";",MATCH(ROW()-2,F$1:F$1000,1)),H2,I2-H2))

In cell M2 you put the following: (note that this is an array formula)
=IF(SUMPRODUCT((J3:J$1000=J2)*(K3:K$1000=K2))>0,"Yes","No")

In cell N2 you put the following: (note that this is an array formula)
=IF(M2="No","31/10/09",INDEX(L3:L$1000,MIN(IF((J3:J$1000=J2)*(K3:K$1000=K2),(ROW(J3:J$1000)-ROW(J2))))))-L2

In D2 you put the following:
=IF(SUMPRODUCT((J$2:J$1000=A2)*(L$2:L$1000=C2)*(M$2:M$1000="Yes")),"Yes","No")

In E2 you put the following: (not that this is an array formula)
=IF(D2="No","31/10/09"-C2,MIN(IF((J$2:J$1000=A2)*(L$2:L$1000=C2)*(M$2:M$1000="Yes")>0,N$2:N$1000,99999)))

Copy cells F2 to N2 down as many rows as you have total number of
procedures. This will produce a table with just one procedure per row.

You may now hide the helper columns F to N if you don't want see them.

Finally copy cells D2 to E2 down as many rows as you have rows of
input data in columns A to C.

Note: You may want to replace the constant "31/10/09" with a reference
to a cell which is easier to change than to change the formulas in
column N and column E.

Hope this helps / Lars-Åke