Unique values with 2 conditions

[chef]

Hi there
Cant get my head around this and would appreciate a little help

I have Column A as employee number which is unique

Column B is a category of absence eg
Sick
Annual Leave
Carer Leave
Parental Leave etc

Column C shows date absence

Employee can have more than one category for different dates but not same date

if I remove duplicates from employee number it defaults to first category and ignores rest and its messy having to repeat and try and build separate remove duplicate filters.


What I would really like to achieve if possible by formula is have all the unique absence categories in another sheet and automatically show the unique count against each absence category when dataset updated

hope I am explaining properly and its achievable.

Many thanks.

 

[chef]

many thanks Fluff as well and appreciate all the help.

 

[chef]

thanks for help already and wonder if another column H could be added to count unique categories against each category only once against any employee
I would like to have a unique category count added if any individual employee has been off in that category at any time but not the number of times for employee and only recorded once in totals.

So for example if I added Emp2 Sick 15/6/2020 1 and Emp1 Annual 18/6/2020 1 into dataset the results in table column G would be the same in Column H in this case as they have already had a episode showing absence against one of the categories on a previous occurrence and not pick up the additional entries. Another example to get over what I would try and get results in column H to change is adding Emp3 Carer 15/6 2020 to dataset, this would show the same results in Column H in as they have already been off on Carer category but if I enter Emp 3 Sick 16/6/20 and Emp 3 Sick 17/6/2020 1 then the table would change and add 1 to totals in column H as Emp2 not been off in that category previously.
In the above entries the only change would be a unique occurance of Emp 3 of sick as 1 added to totals Column H

Sick 3
Annual 2
Carer 1

Hope makes sense and explained properly.

 

[jasonb75]

Do you need this to work with 2016 or 365?

Are the goalposts likely to move again, or are they set in their final position now?

 

[chef]

sorry.. final position and need to have work in 2016 so other users can access.

 

[jasonb75]

Personally I would use something like this, it might be possible without the extra column on the left, but I've got no practical methods coming to mind.

Book1
	A	B	C	D	E	F	G	H
1	Emp	Leave	Date			Leave	Count	2nd count
2	Emp1	Sick	12/06/2020	1		Sick	2	1
3	Emp2	Annual	12/06/2020	1		Annual	2	2
4	Emp3	Carer	13/06/2020	1		Carer	1	1
5	Emp1	Annual	14/06/2020	1
6	Emp1	Sick	14/06/2020	0
Sheet9

Cell Formulas
Range	Formula
F2:F5	F2	=IFERROR(INDEX($B$1:$B$6,MATCH(TRUE,COUNTIF($F$1:$F1,$B$1:$B$6)=0,0)),"")
G2:G5	G2	=IF(F2="","",COUNTIF($B$2:$B$6,F2))
H2:H5	H2	=IF(F2="","",SUMIF($B$2:$B$6,F2,$D$2:$D$6))
D2:D6	D2	=--(COUNTIFS(A$1:A1,A2,B$1:B1,B2)=0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[chef]

thats great and thank you sooo much for your help and patience on this.

This will save so much time and avoid all the manual work being done.

I really appreciate you taking the time to help me