Your question shows patients only using first names. If you wish to extract all visits for a patient named 'Mark' and there are three different patients named 'Mark', your data extract will be flawed.
In any event here is a method to simply double click any cell and enter the patient name. A message box will then display containing all visits for that patient, regardless of column/row.
Copy the following event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
selPatient = UCase(InputBox("Enter Patient Name", "Search For"))
If selPatient = "" Then
Exit Sub
End If
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
If UCase(cell) = selPatient Then
pVisits = pVisits & Format(Cells(cell.Row, 1).Value, "m/d") & ", "
ctr = ctr + 1
End If
Next
pVisits = Left(pVisits, Len(pVisits) - 3)
If ctr = 0 Then
MsgBox Application.Proper(selPatient) & " not found", _
vbInformation, "No Record"
Target.Offset(1).Select
Exit Sub
End If
MsgBox Application.Proper(selPatient) & Chr(10) & Chr(10) & _
pVisits, vbOKOnly, "Visits"
Target.Offset(1).Select
End Sub
Select the worksheet containing the patient visits to evaluate and right click the sheet tab at the bottom.
Select 'View Code'.
Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').
Close the VBE (red button - top right).
Double click any cell and enter the patient name. The results will be displayed. If there are no visits for the patient name entered, the message box will so state.
Note: the event handler is not case sensitive. If the patient name is John Smith, you can enter the enter the name as: JOHN SMITH, john smith, John Smith, and even JoHn SMiTh.