L
Legacy 461885
Guest
hey everyone,
firstly i'm really new to Excel so I don't know how to use VBA and I would like to be able to solve this with some sort of basic function or CSE cause that's all I really know how to do. I've run into a problem that I need a solution for, and none of the ones I've found online for similar data have been able to work for me.
This really is only because the program that we use at work presents the data in a really unhelpful format so we'll have to get pretty creative to figure this one out I think.
The data I have is presented in list form with everyone's name in A the date that they worked in B the number of hours they worked in C and a description of the tasks they did in D
In our logbook program, when the employee makes an entry they fill out the date and hours they worked, and then check a box for all the events that they completed, and in the log program itself it only counts the hours they worked once, but when you export the data to Excel, it makes a separate entry for each event they completed with the same date and hours worked for each one.
so i'm running a =sumifs() based on the name criteria and a specific date range and it comes back with inflated work hours because of the duplicate entries.
I need a function for the green box (J8) that will sum all the numbers in C:C only for a specific name that I type in the blue box (H8), and only within the date range that I enter in the date boxes (L4 and L5)
The only distinguishing criteria I have is the date entry, I cannot just sum unique numbers in C:C because they employee may work the same hours on two separate days and I need both of them to be counted. and I cannot sum based on unique criteria in D:D because they may perform the same task on different days. the tasks themselves are irrelevant, I just need the hours counted correctly.
if it makes a difference, the data is on a separate sheet from the cells I want the information displayed in but that should be relatively easy for me to format the code correctly for that I think?
I also need the code to be able to handle the data A:A B:B C:C D:D being entire columns because I don't have anyway of knowing how long the lists will be as this will be covering a year or more's worth of log entries, (probably in the nature of 5xxx-6xxx rows)
also I don't have 2019 excel I'm still on the one before that.
thanks for everyone's help with this,
I'd love to be able to get this to work.
josh.
firstly i'm really new to Excel so I don't know how to use VBA and I would like to be able to solve this with some sort of basic function or CSE cause that's all I really know how to do. I've run into a problem that I need a solution for, and none of the ones I've found online for similar data have been able to work for me.
This really is only because the program that we use at work presents the data in a really unhelpful format so we'll have to get pretty creative to figure this one out I think.
The data I have is presented in list form with everyone's name in A the date that they worked in B the number of hours they worked in C and a description of the tasks they did in D
In our logbook program, when the employee makes an entry they fill out the date and hours they worked, and then check a box for all the events that they completed, and in the log program itself it only counts the hours they worked once, but when you export the data to Excel, it makes a separate entry for each event they completed with the same date and hours worked for each one.
so i'm running a =sumifs() based on the name criteria and a specific date range and it comes back with inflated work hours because of the duplicate entries.
I need a function for the green box (J8) that will sum all the numbers in C:C only for a specific name that I type in the blue box (H8), and only within the date range that I enter in the date boxes (L4 and L5)
The only distinguishing criteria I have is the date entry, I cannot just sum unique numbers in C:C because they employee may work the same hours on two separate days and I need both of them to be counted. and I cannot sum based on unique criteria in D:D because they may perform the same task on different days. the tasks themselves are irrelevant, I just need the hours counted correctly.
if it makes a difference, the data is on a separate sheet from the cells I want the information displayed in but that should be relatively easy for me to format the code correctly for that I think?
I also need the code to be able to handle the data A:A B:B C:C D:D being entire columns because I don't have anyway of knowing how long the lists will be as this will be covering a year or more's worth of log entries, (probably in the nature of 5xxx-6xxx rows)
also I don't have 2019 excel I'm still on the one before that.
thanks for everyone's help with this,
I'd love to be able to get this to work.
josh.