grassologist
New Member
- Joined
- Dec 16, 2015
- Messages
- 5
Hello,
I am desperately in need to figure out the following. I am pretty good with Excel and VBA but this is making my head spin.
Here is what I need to do: I am trying to return the distinct "Hours" value in a column. Please see an example below
For example: In the week ending on Jan-4, Jim worked on 2 projects, therefore the data is laid out in 2 separate rows, one per project. I am trying to extract the forecast hours for that week, which I know is 40. However, when I put this in a Pivot Table, it uses the SUM function to return the value of 80 for Jim for that week WHEN it should only return 40 since he couldn't have worked more than 40. I am trying to calculate this value before pulling it in the Pivot table. IF I have already counted the value in column D for any specific person in Column B for that specific week, it should only return that value once and skip / return zero value for any subsequent rows where the person for that week has already been accounted for.
Can someone please help me figure out how to get the result like shown in column F below. I've been trying to figure this out for the last 2 days and I really need some help on this. Thank you so much!
<tbody>
</tbody>
I am desperately in need to figure out the following. I am pretty good with Excel and VBA but this is making my head spin.
Here is what I need to do: I am trying to return the distinct "Hours" value in a column. Please see an example below
For example: In the week ending on Jan-4, Jim worked on 2 projects, therefore the data is laid out in 2 separate rows, one per project. I am trying to extract the forecast hours for that week, which I know is 40. However, when I put this in a Pivot Table, it uses the SUM function to return the value of 80 for Jim for that week WHEN it should only return 40 since he couldn't have worked more than 40. I am trying to calculate this value before pulling it in the Pivot table. IF I have already counted the value in column D for any specific person in Column B for that specific week, it should only return that value once and skip / return zero value for any subsequent rows where the person for that week has already been accounted for.
Can someone please help me figure out how to get the result like shown in column F below. I've been trying to figure this out for the last 2 days and I really need some help on this. Thank you so much!
A | B | C | D | E | F |
Project | Name | Week Ending | Hours Forecast | What I get | What I Need |
ABC | Jim | Jan-4 | 40 | 40 | 40 |
XYZ | Jim | Jan-4 | 40 | 40 | |
ABC | David | Jan-4 | 40 | 40 | 40 |
ABC | Robert | Jan-4 | 40 | 40 | 40 |
XYZ | Jacob | Jan-11 | 24 | 24 | 24 |
ABC | David | Jan-11 | 24 | 24 | 24 |
XYZ | Jacob | Jan-11 | 24 | 24 | |
ABC | Jim | Jan-11 | 24 | 24 | 24 |
XYZ | Jacob | Jan-11 | 24 | 24 |
<tbody>
</tbody>