Though you have not provided a sample workbook so not aware of your sheet layout, just to give you an idea how you can achieve this, here is an example. You can make the changes in the formula as per your original workbook. Assuming that all your patients have the unique patient id.
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
1 |
Patient ID |
L Name |
F Name |
Event Date |
|
Patient ID |
L Name |
F Name |
Event Date |
2 |
1 |
A |
M |
01/09/2014 |
|
1 |
A |
M |
08/09/2014 |
3 |
2 |
B |
S |
02/09/2014 |
|
2 |
B |
S |
07/09/2014 |
4 |
3 |
C |
T |
03/09/2014 |
|
3 |
C |
T |
09/09/2014 |
5 |
1 |
A |
N |
04/09/2014 |
|
4 |
D |
R |
06/09/2014 |
6 |
3 |
C |
T |
05/09/2014 |
|
|
|
|
|
7 |
4 |
D |
R |
06/09/2014 |
|
|
|
|
|
8 |
2 |
B |
S |
07/09/2014 |
|
|
|
|
|
9 |
1 |
A |
M |
08/09/2014 |
|
|
|
|
|
10 |
3 |
C |
T |
09/09/2014 |
|
|
|
|
|
Formulas used are as below.
In G2
=INDEX(B$2:B$10,MATCH($F2,$A$2:$A$10,0))
and then copy right to H2 and then down to row 5.
In I2
=INDEX($D$2:$D$10,MAX(INDEX(($A$2:$A$10=F2)*(ROW($A$2:$A$10)-ROW($A$2)+1),0)))
and then copy down.
Bookmarks