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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,490
Office Version
  1. 365
Platform
  1. Windows
Pivot table?
Unique employee number as primary row header.
Absence category as secondary row header.
Count of absence category for values.
 

chef

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

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

 

chef

Well-known Member
Joined
Jul 10, 2002
Messages
561
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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

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

Well-known Member
Joined
Jul 10, 2002
Messages
561
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,490
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1EmpLeaveDateLeaveCount
2Emp1Sick12/06/2020Sick2
3Emp2Annual12/06/2020Annual2
4Emp3Carer13/06/2020Carer1
5Emp1Annual14/06/2020  
6Emp2Sick14/06/2020
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))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

chef

Well-known Member
Joined
Jul 10, 2002
Messages
561
Office Version
  1. 365
  2. 2016
wow...thank you so much....

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
With the dynamic functions

+Fluff New.xlsm
ABCDEFG
1EmpLeaveDateLeaveCount
2Emp1Sick12/06/2020Sick2
3Emp2Annual12/06/2020Annual2
4Emp3Carer13/06/2020Carer1
5Emp1Annual14/06/2020
6Emp2Sick14/06/2020
7
8
Main
Cell Formulas
RangeFormula
F2:F4F2=UNIQUE(FILTER(B2:B10,B2:B10<>""))
G2:G4G2=COUNTIF($B$2:$B$6,F2#)
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,020
Messages
5,628,181
Members
416,297
Latest member
Kara Payne

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