Covid Vaccine Next dose

[abarodi1]

Hi,

I'm a healthworker and i relly need your help guys. I have the sample data (please see attached image). The real data consist of thousands of patients but i will give 3 patients only for the benefit of time.

What I want to achieve guys is to show only the row where the recent or last dose of the patient Given 3 patients below
1. Mr. Wick has 1st and 2nd dose so I want to retain only the row 4 where the recent dose of Mr. Wick is 2.
2. Mr. Reeves has 1, 2, and 3 dose so i want to retain only the data on row 7.
3. Mr. Trump has only 1 dose so i want to retain the data of row 8.

I hope you guys can help us. We really need this to monitor our patient's next dose.

God bless and thank you!

 

[bebo021999]

Have you tried COUNTIFS?
In E13:
=COUNTIFS($A$3:$A$8,$A13,$B$3:$B$8,$B13)

 

[AhoyNC]

Try:
This should work in Excel 2010 and higher.
You will need to enter the formula with CTRL-SHIFT-ENTER.
But the formula in cell C13 (change to match your actual data) and then drag (not copy) the formula down the rows.

Book4
	A	B	C	D	E
1		SAMPLE DATA
2	LAST NAME	FIRST NAME	DATE	Vaccine	DOSE
3	WICK	JOHN	1/1/2021	Pfizer	1
4	WICK	JOHN	3/3/2021	Pfizer	2
5	REEVES	KEANU	1/1/2021	Pfizer	1
6	REEVES	KEANU	3/3/2021	Pfizer	2
7	REEVES	KEANU	5/5/2021	Pfizer	3
8	TRUMP	DONALD	1/1/2021	Pfizer	1
9
10
11		OUPUT WANT TO SHOW
12	LAST NAME	FIRST NAME	DATE	Vaccine	DOSE
13	WICK	JOHN	3/3/2021	Pfizer	2
14	REEVES	KEANU	5/5/2021	Pfizer	3
15	TRUMP	DONALD	1/1/2021	Pfizer	1
Sheet4

Cell Formulas
Range	Formula
C13:E15	C13	=INDEX($C$3:$E$8,AGGREGATE(14,6,(ROW($C$3:$C$8)-ROW($C$3)+1)/(MAX(IF(($A13=$A$3:$A$8)*($B13=$B$3:$B$8),$E$3:$E$8))=$E$3:$E$8),1),{1,2,3})
Dynamic array formulas.