I have a large spreadsheet (20,000 records) of patient records. I need to
search the records for specific patient IDs and then analyse certain fields
of data in the patient recordset but I'm not quite sure how to go about it.

I'd like to have some code/macro that opens a dialog box requesting my input
for a PatientID field. The code should then locate the relevant records if
that patient exists and if it does look at all the records matching that
PatientID and give me the lowest value of another field (Temp) in that group
of records for that patient.

At present I just use FIND from the menu bar then eyeball the data looking
for my lowest value, but it would be nice to have it automated.

I look forward to any suggestions,

TIA,

Ian.

Can you sort the list of records? If so, I would suggest sorting by
PatientID first, then Temp 2nd, both ascending. Then finding the first line
for a given PatientID would automatically bring you to the line with the
lowest Temp. The code could be as simple as:

PatientID = InputBox("Enter the Patient ID:")
Set ResultRange = Range("A1:D7").Columns(1).Find(PatientID)
MinTemp = ResultRange.Cells(1, 4) ' Adjust the column to match your Temp
column

HTH! And, by the way, I work at a hospital, so I am also used to searching
patient records!!!

"Ian" wrote:

I have a large spreadsheet (20,000 records) of patient records. I need to
search the records for specific patient IDs and then analyse certain fields
of data in the patient recordset but I'm not quite sure how to go about it.

I'd like to have some code/macro that opens a dialog box requesting my input
for a PatientID field. The code should then locate the relevant records if
that patient exists and if it does look at all the records matching that
PatientID and give me the lowest value of another field (Temp) in that group
of records for that patient.

At present I just use FIND from the menu bar then eyeball the data looking
for my lowest value, but it would be nice to have it automated.

I look forward to any suggestions,

TIA,

Ian.

Thanks for the suggestions, that all seems to make sense except I'm a little
lost as to where the code should go, I'm more used to Access than Excel. I
tried putting a button on the worksheet with the code in the click event. I
got the dialog box but then an error: "Object variable or With block variable
not set" referring to the "MinTemp = " line. Sorry if I'm being thick!!

Ian.

"K Dales" wrote:

Can you sort the list of records? If so, I would suggest sorting by
PatientID first, then Temp 2nd, both ascending. Then finding the first line
for a given PatientID would automatically bring you to the line with the
lowest Temp. The code could be as simple as:

PatientID = InputBox("Enter the Patient ID:")
Set ResultRange = Range("A1:D7").Columns(1).Find(PatientID)
MinTemp = ResultRange.Cells(1, 4) ' Adjust the column to match your Temp
column

HTH! And, by the way, I work at a hospital, so I am also used to searching
patient records!!!

"Ian" wrote:

I have a large spreadsheet (20,000 records) of patient records. I need to
search the records for specific patient IDs and then analyse certain fields
of data in the patient recordset but I'm not quite sure how to go about it.

I'd like to have some code/macro that opens a dialog box requesting my input
for a PatientID field. The code should then locate the relevant records if
that patient exists and if it does look at all the records matching that
PatientID and give me the lowest value of another field (Temp) in that group
of records for that patient.

At present I just use FIND from the menu bar then eyeball the data looking
for my lowest value, but it would be nice to have it automated.

I look forward to any suggestions,

TIA,

Ian.