How to count number of presents and absents in Excel

Sai — Sun, 26 Jun 2022 16:10:00 GMT

Hi Team,
I have an employee excel file ,there i have to find out how many days an employee "Present & Absent'.
Note; p=present, A=Absent. for reference please find below data.

------------------------------
Sai
------------------------------

RE: How to count number of presents and absents in Excel

devneetmohanty07 — Sun, 26 Jun 2022 20:21:00 GMT

Hi Sai,

For your requirement, I created a workflow as below:

My input excel file looks something like this:

In the first half of the workflow, I am reading the data from excel using basic excel actions such as Create Instance, Open Workbook, Get Worksheet As Collection and Close Instance to get desired dataset into a collection called as Data:

Once, I have my Data Collection ready, I am iterating through my data collection using a Loop stage first. Within the loop stage, I use the 'Filter Collection' action from 'Utility - Collection Manipulation' business object to get a collection named 'Filtered Name Collection'. This collection will consist of the record from the Data collection where Name column is equal to the current iteration Name column value. Below are the filter parameters:

'Filter Name Collection' current value after executing the action will be:

Now, I use the 'Transpose Collection' action from 'Utility - Collection Manipulation' business object to get a collection named 'Transposed Collection' by providing the input collection as the 'Filtered Name Collection' which I generated in my prior action . The action parameters are as below:

'Transposed Collection' current value after executing the action will be:

Now, I again use the 'Filter Collection' action from 'Utility - Collection Manipulation' business object to get a collection named 'Absentees Collection'. This collection will consist of the record from the 'Transposed Collection' generated from the prior action where Value column is equal to 'A' . Below are the filter parameters:

'Absentees Collection' current value after executing the action will be:

Next, I again use the 'Filter Collection' action from 'Utility - Collection Manipulation' business object to get a collection named 'Presentees Collection'. This collection will consist of the record from the 'Transposed Collection' generated from the prior action where Value column is equal to 'P' . Below are the filter parameters:

'Presentees Collection' current value after executing the action will be:

Now, since I have two collections with me which provide me rows with presentees records and absentees records, so I can simply get the count of rows for each of these collections into separate data items:

At the end, I add rows to a Defined collection called as 'Final Collection' with three fields namely, Name, Number Of Absent Days and Number Of Present Days:

At the end of the workflow execution I get the Final Collection as follows:

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

topic RE: How to count number of presents and absents in Excel in Product Forum

How to count number of presents and absents in Excel

RE: How to count number of presents and absents in Excel