Multiple criteria to return a date and name

[howden]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Patient Name (or Id)
[/TD]
[TD]Vaccine Name
[/TD]
[TD]date of vaccine
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sally Smith
[/TD]
[TD]Mumps
[/TD]
[TD]5/17/13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sally Smith
[/TD]
[TD]Rubella
[/TD]
[TD]5/17/13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sally Smith
[/TD]
[TD]Measles
[/TD]
[TD]5/17/13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Doe
[/TD]
[TD]Mumps
[/TD]
[TD]5/17/13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JohnDoe
[/TD]
[TD]Measles
[/TD]
[TD]5/17/13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sally Smith
[/TD]
[TD]Measles
[/TD]
[TD]6/4/1990
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












Hi everyone,

First time post. Having a bit of trouble with deciding whether I need to use VBA or standard formulas for this criteria

I have a sheet where I need to identify different vaccines for people and group them as one record. I would like to identify any time a person received a Measles, Mumps, and Rubella vaccine on the same day and return a new record with the same date with the vaccine name as MMR

This is the formula I was using but I couldn't figure out my argument:

SMALL(IF((AND($F$2:$F$28134,$X$2,$F$2:$F$28134=$Y$2,$F$2:$F$28134=$Z$2),A2:$A$28134,MMR)))

It is definitely wrong but I was hoping someone could help me out! Thanks!

 

[VBA Geek]

hi

what would you like your output to look like?

 

[howden]

Ideally I would like the output after to look like this:
Thank you!!
[TABLE="class: cms_table_grid, align: left"]
<tbody>[TR]
[TD]Sally Smith[/TD]
[TD]MMR
[/TD]
[TD]5/17/13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Mumps[/TD]
[TD]5/17/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JohnDoe[/TD]
[TD]Measles[/TD]
[TD]5/17/13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sally Smith
[/TD]
[TD]Measles[/TD]
[TD]6/4/1990
[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Do you have them already group and just want to look up whether he had a MMR on the same day or you need grouping also?

 

[howden]

if they could be grouped that would be great.

 

[VBA Geek]

If your 3 columns are in A B C, starting from A1, then try:

Sub feef()
    Dim iRow As Long
iRow = Range("A:A").Find("*", , , , 1, 2).Row
Range("D2:D" & iRow).Formula = "=SUM(COUNTIFS(A$2:A$" & iRow & ",$A2,$C$2:$C$" & iRow & ",$C2,$B$2:$B$" & iRow & ",{""Mumps"",""Rubella"",""Measles""}))"
Range("B2:B" & iRow) = Evaluate("IF(D2:D" & iRow & "=3, ""MMR"", B2:B" & iRow & ")")
Range("D1:D" & iRow).Clear
Range("A1:C" & iRow).RemoveDuplicates Array(1, 2, 3), 1
End Sub