create a

[Theo Brandt]

Hi there.
I have a sheet 'events' with 'A' Row being a patient ID taking the
form S002, S003 etc. Each patient can have more than one row- these
are hospital events, so often more than one.

I have another sheet 'selected' with a selected list of patient IDs.
Some of these patients have an event or events on the 'events' page,
some do not.
I'd like to create another sheet where all 'events' rows with a
matching ID to the 'selected' list are displayed.
(i.e. aselected sheet of rows from 'patients' using the ID from
'selected' as the criteria

any suggesttions? thought about lookups but the mutiple rows stumped
me!

 

[Pete_UK]

You can create a unique sequential reference to each entry in the
events sheet using a formula like this in a helper column (assume
column Z, cell Z2):

=IF(A2="","",A2&"_"&COUNTIF(A$2:A2,A2))

then copy this down as far as you like. It will give you something
like:

S001_1
S002_1
S001_2
S003_1
S001_3

and so on.

Then assume in another sheet that you use A1 to select the patient
number. On sucessive rows in that sheet you can generate the unique
code again for each patient by means of this formula in A2:

=IF(A$1="","",A$1&"_"&ROW(A1))

and then you can use this as the basis of your lookup (using an INDEX/
MATCH combination, looking for an exact match).

Hope this helps you on your way.

Pete