Sorting with VBA (Tough)

[Msgbox Data not found]

A=FA, B= SO, C=DA, X=FA, Y=SO, Z=DA
CLR = Cleared,
DNS = Document not submitted
DS = Document submitted
SCH = Scheduled for Medical Examination
Rltn = Relation

1.
Applicant Medical Hold Rltn Applicant Medical Hold
A CLR DNS SO B SCH DNS
A CLR DNS DA C CLR DS
C CLR DS FA A CLR DNS
C CLR DS BR B SCH DN
----------------------------------------------------------------------------------------------
2.
X CLR DS SO Y
CLR DS
X CLR DS DA Z
CLR DS
Y CLR DS FA X
CLR DS
Y CLR DS SI Z
CLR DS
Z CLR DS FA X
CLR DS
Z CLR DS BR Y
CLR DS

The above table 1 and 2 has been numbered for clear clarification. Actually
in the spreadsheet they appear in the same sheet.
I have two short out the individuals in a different sheet named "Medical
Cleared".
The condition is that if any of the family members have no medical clearance
and DNS then they will not go for the "Medical Cleared" Sheet, even though
one of them have Medical clearance and DNS. All of them should have Medical
CLR and DS. Therfore in case ABC not qualified to go for the Med Clr sheet
but for XYZ, they deserve to go for MedClr sheet as they have Medical Cleared
and DS.
We use Column A,B,C,D,E,F,G in the Excel Sheet.
Can anyone help me with this problem to solve in VBA???

Please!!!