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.

 

[jasonb75]

Pivot table?
Unique employee number as primary row header.
Absence category as secondary row header.
Count of absence category for values.

 

[chef]

sorry I'm not looking to display the employee numbers as such and was hoping to be able to have the absence category in one column and a unique count against in the next column.

Was also trying to integrate this as part of a dashboard report and automate and would prefer not to use a pivot table if possible.
I have an employee database of over 13,000 staff who can potentially be off at any time so showing all employee number in pivot primary row header not really desirable.

 

[jasonb75]

I only said to use the unique employee number because you made a point of mentioning it twice in your post, which made it a fair assumption that you wanted the results broken down by employee as well as absence type.

To avoid any more confusion, please post a fictional example of your data source and a mock up of the expected output for that example using XL2BB (link below).

Please also update your user profile to show which version(s) of excel you are using by clicking your username at the top right of the page, then 'account details', remember to save changes after updating.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[chef]

sorry for the confusion

quick example would be

A B C D

Emp1 Sick 12/6/20 1
Emp2 Annual Leave 12/6/20 1
Emp3 Carer Leave 13/6/20 1
Emp1 Annual Leave 14/6/20 1
Emp2 Sick leave 14/6/20 1


Result looking for on another sheet -

Sick 2
Annual Leave 2
Carer leave 1

Apologies for not explaining properly...Its more about getting the unique category count and not to break down by employee nor date

 

[jasonb75]

What do the numbers in column D show? If those numbers are greater than 1, should the output be the sum of those numbers or the count of the rows?

 

[chef]

No the number is always 1 and just the count of these rows

 

[jasonb75]

This should do what you need, you could simplify the formulas with the UNIQUE and / or FILTER functions in office 365 but that will not work with older versions.

Book1
	A	B	C	D	E	F	G
1	Emp	Leave	Date			Leave	Count
2	Emp1	Sick	12/06/2020			Sick	2
3	Emp2	Annual	12/06/2020			Annual	2
4	Emp3	Carer	13/06/2020			Carer	1
5	Emp1	Annual	14/06/2020
6	Emp2	Sick	14/06/2020
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))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[chef]

wow...thank you so much....

I so appreciate your help and patience and I would never have figured that out.

 

[Fluff]

With the dynamic functions

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	Emp	Leave	Date			Leave	Count
2	Emp1	Sick	12/06/2020			Sick	2
3	Emp2	Annual	12/06/2020			Annual	2
4	Emp3	Carer	13/06/2020			Carer	1
5	Emp1	Annual	14/06/2020
6	Emp2	Sick	14/06/2020
7
8
Main

Cell Formulas
Range	Formula
F2:F4	F2	=UNIQUE(FILTER(B2:B10,B2:B10<>""))
G2:G4	G2	=COUNTIF($B$2:$B$6,F2#)
Dynamic array formulas.