Excel Help Needed

[Dposd]

New here, so hello all.



Wall off text for an explanation.

Tldr Trying to get excel to count same employee listed multiple times while analyzing data.



Trying to get help with a data question. I run a business and i assign employees to a work site on a weekly basis. Sometimes they get reassigned to another site or just assist another site throughout the week. I have a program that provides weekly data showing which job site they worked and any leave they have taken but it is broken down by the day. It also comes on individual tables, 1 for leave taken and each worksite has a table for employees paid to that site (pay is specific to the worksite). When compiled the employees will have multiple rows for them for the different days of the week. I’m trying to analyze how the employee is paid every week ( example, was the employee paid 90% to the assigned site and 10% leave). Not having trouble with that part but I also track how many employees i assign to each site every week but am having a hard time figuring out how to count someone just once instead of the multiple times they show up due to the daily breakdown. Ultimately I want to be able to say something like:I had 30 employees assigned to worksite “a” for the week of 9/4/23 and for the week they spent 80% of the time on worksite a, 15% of the time at worksite b, and 5% on leave (data provided from the weekly data explained above). Thoughts?

 

[etaf]

are you stil using 2016 version or a later version , if later then unique would help here

but
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Dposd]

I do not have access to those files right now. Right now I have limited access. But it is just a simple table that would look similar to what is below. In this case I would want "whatever works here" to only count Joe Smith once so that I can consider him 1 employee Assigned to worksite A (all employees would be on the table as well as other Work Week's so that I can analyze it for various periods). The names appear multiple times due to the data on the sheet being fed from other tables (1 for leave and others for the individual worksites). Ultimately, I am trying to use the data below to say I had x amount of employees assigned to Worksite A for x time period and here is the breakdown on how the time was utilized throughout the x time period. I just don't know how to get it to count the employees just once/work week.

Work Week	Full Name	Worksite Assigned	Charge Date	Charged Hours	Charged To
9/4/2023​	Smith, Joe	A	9/4/2023​	8​	Worksite A
9/4/2023​	Smith, Joe	A	9/5/2023​	6​	Worksite A
9/4/2023​	Smith, Joe	A	9/5/2023​	2​	Worksite B
9/4/2023​	Smith, Joe	A	9/6/2023​	8​	Worksite A
9/4/2023​	Smith, Joe	A	9/7/2023​	8​	Leave
9/4/2023​	Smith, Joe	A	9/8/2023​	4​	Worksite A
9/4/2023​	Smith, Joe	A	9/8/2023​	4​	Leave

 

[Michael M]

A pivot table should do this.