How to consolidate multiple employee data

kaz123

New Member
Joined
Oct 8, 2017
Messages
14
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 IDEmployee NameAbsence TypeAbsence start dateabsence end dateTotal number of days absentExcluding number of allowance days
123456​
John SmithSickness
01/01/20​
15/01/20​
15​
5​
123456​
John SmithSickness
10/02/20​
11/02/20​
2​
2​
987654​
Sheila BrownSickness
01/01/20​
05/01/20​
5​
0​
987654​
Sheila BrownSickness
15/02/20​
15/02/20​
1​
0​
987654​
Sheila BrownMaternity
25/02/20​
27/02/20​
3​
0​
987654​
Sheila BrownMaternity
01/03/20​
15/03/20​
15​
8​
Desired table
Employee IDEmployee NameAbsence TypeTotal number of days absentnumber of days excluded after allowance
123456​
John SmithSickness
17​
7​
987654​
Sheila BrownSickness
6​
0​
987654​
Sheila BrownMaternity
18​
8​
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1Employee IDEmployee NameAbsence TypeAbsence start dateabsence end dateTotal number of days absentExcluding number of allowance days
2123456John SmithSickness01/01/2015/01/20155
3123456John SmithSickness10/02/2011/02/2022
4987654Sheila BrownSickness01/01/2005/01/2050
5987654Sheila BrownSickness15/02/2015/02/2010
6987654Sheila BrownMaternity25/02/2027/02/2030
7987654Sheila BrownMaternity01/03/2015/03/20158
8
9
10Example 1
11Employee IDEmployee NameAbsence TypeTotal Daysexcluded
12123456John SmithSickness177
13987654Sheila BrownSickness60
14987654Sheila BrownMaternity188
15   
16Example 2 - Excel version 365 with UNIQUE function 
17Employee IDEmployee NameAbsence TypeTotal Daysexcluded
18123456John SmithSickness177
19987654Sheila BrownSickness60
20987654Sheila BrownMaternity188
Sheet2
Cell Formulas
RangeFormula
B12:B14B12=VLOOKUP(A12,$A$2:$B$7,2,0)
E12:E14, E18:E20E12=IF(D12>10,D12-10,0)
A12:A15A12=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:C15C12=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:D20D12=SUMIFS($F$2:$F$7,$A$2:$A$7,A12,$C$2:$C$7,C12)
D15:D16D15=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))),"")
A18A18=UNIQUE(A2:C7)
 

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
236
Why go for complicated formulas when a simple pivot table will do the same? Probably faster at that.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
with Power Query

Employee IDEmployee NameAbsence TypeAbsence start dateabsence end dateTotal number of days absentExcluding number of allowance days
123456John SmithSickness01/01/202015/01/2020155
123456John SmithSickness10/02/202011/02/202022
987654Sheila BrownSickness01/01/202005/01/202050
987654Sheila BrownSickness15/02/202015/02/202010
987654Sheila BrownMaternity25/02/202027/02/202030
987654Sheila BrownMaternity01/03/202015/03/2020158
Employee IDEmployee NameAbsence TypeTotal number of days absentExcluding number of allowance days
123456John SmithSickness177
987654Sheila BrownSickness60
987654Sheila BrownMaternity188

Rich (BB code):
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

New Member
Joined
Oct 8, 2017
Messages
14

ADVERTISEMENT

with Power Query

Employee IDEmployee NameAbsence TypeAbsence start dateabsence end dateTotal number of days absentExcluding number of allowance days
123456John SmithSickness01/01/202015/01/2020155
123456John SmithSickness10/02/202011/02/202022
987654Sheila BrownSickness01/01/202005/01/202050
987654Sheila BrownSickness15/02/202015/02/202010
987654Sheila BrownMaternity25/02/202027/02/202030
987654Sheila BrownMaternity01/03/202015/03/2020158
Employee IDEmployee NameAbsence TypeTotal number of days absentExcluding number of allowance days
123456John SmithSickness177
987654Sheila BrownSickness60
987654Sheila BrownMaternity188

Rich (BB code):
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

New Member
Joined
Oct 8, 2017
Messages
14

ADVERTISEMENT

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!
 

kaz123

New Member
Joined
Oct 8, 2017
Messages
14
Ok understood, I was hoping there was a way to invoke the query in a regular Excel VBA module
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

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