Finding data sequentially

[jimbo]

I have a month of dates across columns by patient (in rows). In each
of these rows there is one date where a test was cancelled. Is there a
reasonable way to determine where the cell(s) that have the word
"cancelled" are relative to the numerical result(s) are in the month?
I'm trying to diffetentiate reorders that are caused by cancellations
from reorders due to patient condition. I realize that this will not
answer the question, but I can break out into two groups if I can tell
which came first, the result or the cancellation

 

[James Ravenswood]

I have a month of dates across columns by patient (in rows). In each
of these rows there is one date where a test was cancelled. Is there a
reasonable way to determine where the cell(s) that have the word
"cancelled" are relative to the numerical result(s) are in the month?
I'm trying to diffetentiate reorders that are caused by cancellations
from reorders due to patient condition. I realize that this will not
answer the question, but I can break out into two groups if I can tell
which came first, the result or the cancellation

Hi jimbo:

Here is a typical way to find the date associated with the occurance
of some text. Say we use col B for patient names. In C1 thru AG1 we
enter the monthly dates:
1/1/2011 1/2/2011 1/3/2011 1/4/2011 1/5/2011 etc.

For example, B2 may contain "James" and I2 may contain the word
"cancelled"
In A2 enter:
=OFFSET($C$1,0,MATCH("cancelled",C2:AG2,0)-1) and format A2 as a Date
This displays 1/7/2011

The formula looks across the row to find "cancelled" and then looks up
to the first row to retrieve the date.

An error means that there was no "cancelled in the row>