Unique values with 2 conditions

chef

Well-known Member
Joined
Jul 10, 2002
Messages
561
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Jul 10, 2002
Messages
561
Office Version
  1. 365
  2. 2016
many thanks Fluff as well and appreciate all the help.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

chef

Well-known Member
Joined
Jul 10, 2002
Messages
561
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 10, 2002
Messages
561
Office Version
  1. 365
  2. 2016
sorry.. final position and need to have work in 2016 so other users can access.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1EmpLeaveDateLeaveCount2nd count
2Emp1Sick12/06/20201Sick21
3Emp2Annual12/06/20201Annual22
4Emp3Carer13/06/20201Carer11
5Emp1Annual14/06/20201   
6Emp1Sick14/06/20200
Sheet9
Cell Formulas
RangeFormula
F2:F5F2=IFERROR(INDEX($B$1:$B$6,MATCH(TRUE,COUNTIF($F$1:$F1,$B$1:$B$6)=0,0)),"")
G2:G5G2=IF(F2="","",COUNTIF($B$2:$B$6,F2))
H2:H5H2=IF(F2="","",SUMIF($B$2:$B$6,F2,$D$2:$D$6))
D2:D6D2=--(COUNTIFS(A$1:A1,A2,B$1:B1,B2)=0)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

chef

Well-known Member
Joined
Jul 10, 2002
Messages
561
Office Version
  1. 365
  2. 2016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,681
Messages
5,626,254
Members
416,168
Latest member
DROP_DATABASE_MrExel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top