How to consolidate multiple employee data

[kaz123]

Hi

I am trying to consolidate employee multiple absences into 1 single rows for absence of the same type. Employees are allowed an allowance of 10 days absences (of each absence type) so anything above that will be showing as excluded.

The table below is what the data looks like and further below is what I am looking to get the data to look like. For example if the employee has more than one instance of the same absence type (e.g. Sickness) then these should be consolidate on one line.

Many thanks

Current table
Employee ID	Employee Name	Absence Type	Absence start date	absence end date	Total number of days absent	Excluding number of allowance days
123456​	John Smith	Sickness	01/01/20​	15/01/20​	15​	5​
123456​	John Smith	Sickness	10/02/20​	11/02/20​	2​	2​
987654​	Sheila Brown	Sickness	01/01/20​	05/01/20​	5​	0​
987654​	Sheila Brown	Sickness	15/02/20​	15/02/20​	1​	0​
987654​	Sheila Brown	Maternity	25/02/20​	27/02/20​	3​	0​
987654​	Sheila Brown	Maternity	01/03/20​	15/03/20​	15​	8​
Desired table
Employee ID	Employee Name	Absence Type	Total number of days absent	number of days excluded after allowance
123456​	John Smith	Sickness	17​	7​
987654​	Sheila Brown	Sickness	6​	0​
987654​	Sheila Brown	Maternity	18​	8​

 

[AhoyNC]

It always helps to tell us the version of Excel you are using.
If you have Excel 365 with the UNIQUE function then example 2 below.
Any other version of Excel you can try example 1. In example 1 column A (Employee ID) and column C (Absence Type) are array formulas that must be entered with CTRL-SHIFT-ENTER then drag formula down rows as needed.

Book1
	A	B	C	D	E	F	G
1	Employee ID	Employee Name	Absence Type	Absence start date	absence end date	Total number of days absent	Excluding number of allowance days
2	123456	John Smith	Sickness	01/01/20	15/01/20	15	5
3	123456	John Smith	Sickness	10/02/20	11/02/20	2	2
4	987654	Sheila Brown	Sickness	01/01/20	05/01/20	5	0
5	987654	Sheila Brown	Sickness	15/02/20	15/02/20	1	0
6	987654	Sheila Brown	Maternity	25/02/20	27/02/20	3	0
7	987654	Sheila Brown	Maternity	01/03/20	15/03/20	15	8
8
9
10	Example 1
11	Employee ID	Employee Name	Absence Type	Total Days	excluded
12	123456	John Smith	Sickness	17	7
13	987654	Sheila Brown	Sickness	6	0
14	987654	Sheila Brown	Maternity	18	8
15
16	Example 2 - Excel version 365 with UNIQUE function
17	Employee ID	Employee Name	Absence Type	Total Days	excluded
18	123456	John Smith	Sickness	17	7
19	987654	Sheila Brown	Sickness	6	0
20	987654	Sheila Brown	Maternity	18	8
Sheet2

Cell Formulas
Range	Formula
B12:B14	B12	=VLOOKUP(A12,$A$2:$B$7,2,0)
E12:E14, E18:E20	E12	=IF(D12>10,D12-10,0)
A12:A15	A12	=IFERROR(INDEX($A$2:$A$7,SMALL(IF(FREQUENCY(MATCH($A$2:$A$7&$C$2:$C$7,$A$2:$A$7&$C$2:$C$7,0),ROW($A$2:$A$7)-ROW($A$2)+1),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($A$12:A12))),"")
C12:C15	C12	=IFERROR(INDEX($C$2:$C$7,SMALL(IF(FREQUENCY(MATCH($A$2:$A$7&$C$2:$C$7,$A$2:$A$7&$C$2:$C$7,0),ROW($A$2:$A$7)-ROW($A$2)+1),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($A$12:A12))),"")
D12:D14, D18:D20	D12	=SUMIFS($F$2:$F$7,$A$2:$A$7,A12,$C$2:$C$7,C12)
D15:D16	D15	=IFERROR(INDEX($A$2:$A$7,SMALL(IF(FREQUENCY(MATCH($A$2:$A$7&$C$2:$C$7,$A$2:$A$7&$C$2:$C$7,0),ROW($A$2:$A$7)-ROW($A$2)+1),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($D$12:D15))),"")
A18	A18	=UNIQUE(A2:C7)

 

[petertenthije]

Why go for complicated formulas when a simple pivot table will do the same? Probably faster at that.

 

[sandy666]

with Power Query

Employee ID	Employee Name	Absence Type	Absence start date	absence end date	Total number of days absent	Excluding number of allowance days
123456	John Smith	Sickness	01/01/2020	15/01/2020	15	5
123456	John Smith	Sickness	10/02/2020	11/02/2020	2	2
987654	Sheila Brown	Sickness	01/01/2020	05/01/2020	5	0
987654	Sheila Brown	Sickness	15/02/2020	15/02/2020	1	0
987654	Sheila Brown	Maternity	25/02/2020	27/02/2020	3	0
987654	Sheila Brown	Maternity	01/03/2020	15/03/2020	15	8
Employee ID	Employee Name	Absence Type	Total number of days absent	Excluding number of allowance days
123456	John Smith	Sickness	17	7
987654	Sheila Brown	Sickness	6	0
987654	Sheila Brown	Maternity	18	8

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Employee ID", "Employee Name", "Absence Type"}, {{"Total number of days absent", each List.Sum([Total number of days absent]), type number}, {"Excluding number of allowance days", each List.Sum([Excluding number of allowance days]), type number}})
in
    Group

 

[kaz123]

with Power Query

Employee ID	Employee Name	Absence Type	Absence start date	absence end date	Total number of days absent	Excluding number of allowance days
123456	John Smith	Sickness	01/01/2020	15/01/2020	15	5
123456	John Smith	Sickness	10/02/2020	11/02/2020	2	2
987654	Sheila Brown	Sickness	01/01/2020	05/01/2020	5	0
987654	Sheila Brown	Sickness	15/02/2020	15/02/2020	1	0
987654	Sheila Brown	Maternity	25/02/2020	27/02/2020	3	0
987654	Sheila Brown	Maternity	01/03/2020	15/03/2020	15	8
Employee ID	Employee Name	Absence Type	Total number of days absent	Excluding number of allowance days
123456	John Smith	Sickness	17	7
987654	Sheila Brown	Sickness	6	0
987654	Sheila Brown	Maternity	18	8

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Employee ID", "Employee Name", "Absence Type"}, {{"Total number of days absent", each List.Sum([Total number of days absent]), type number}, {"Excluding number of allowance days", each List.Sum([Excluding number of allowance days]), type number}})
in
    Group

Thanks for all your help. Re PowerQuery, how do I go about using the code in a PowerQuery?

 

[sandy666]

your source data should be as Excel Table (Ctrl+T)
check in Name Manager about the name, usually it will be Table1 (if not, change the name in the code suitably)
then
copy code from the post
from Data tab : New Query - From Other Sources - Blank Query
it will open Power Query Editor
find Advanced Editor and paste copied code there
ok

 

[kaz123]

your source data should be as Excel Table (Ctrl+T)
check in Name Manager about the name, usually it will be Table1 (if not, change the name in the code suitably)
then
copy code from the post
from Data tab : New Query - From Other Sources - Blank Query
it will open Power Query Editor
find Advanced Editor and paste copied code there
ok

Thanks a lot, this work very nicely. Appreciate yours and everyone else's help here. Now all I need to learn is how to automate/execute this PowerQuery via a macro button!

 

[sandy666]

fortunately vba is not supported in Power Query

 

[kaz123]

Ok understood, I was hoping there was a way to invoke the query in a regular Excel VBA module