Return Distinct Value based on Criteria in two other columns

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!


ABCDEF
ProjectNameWeek EndingHours ForecastWhat I getWhat I Need
ABCJim
Jan-4404040
XYZJimJan-44040
ABCDavidJan-4404040
ABCRobertJan-4404040
XYZJacobJan-11242424
ABCDavidJan-11242424
XYZJacobJan-112424
ABCJimJan-11242424
XYZJacobJan-112424

<tbody>
</tbody>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi Grassologist,

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.

Use Max option in pivot table instead of Sum and you will get 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.

Need example for this.

Regards,
DILIPandey
 
Upvote 0
Looks like...

In E2 control+shift+enter, not just enter, and copy down:
Rich (BB code):

=IF(ISNUMBER(MATCH($B2&"|"&$C2,$B$1:B1&"|"&$C$1:C1,0)),"",
  VLOOKUP($B2&"|"&$C2,CHOOSE({1,2},$B$2:$B$10&"|"&$C$2:$C$10,$D$2:$D$10),2,0))
 
Upvote 0
Looks like...

In E2 control+shift+enter, not just enter, and copy down:
Code:

=IF(ISNUMBER(MATCH($B2&"|"&$C2,$B$1:B1&"|"&$C$1:C1,0)),"",

VLOOKUP($B2&"|"&$C2,CHOOSE({1,2},$B$2:$B$10&"|"&$C$2:$C$10,$D$2:$D$10),2,0))

Dr. Aladin

Your solution as always very fascinating for newbies like me :) took me an hour to crunch it.

one thing i still cannot comprehended is this:

CHOOSE({1,2},$B$2:$B$10&"|"&$C$2:$C$10,$D$2:$D$10)

when i analyze it in Evaluate formula option; i can understand it creates an array of two columns, but i cant get it its like magic..
how did the CHOOSE function interacted with this operator {} ? how did it understand that this operator means create an array of two columns one consist of the resultant of this $B$2:$B$10&"|"&$C$2:$C$10 and the other column consist of this $D$2:$D$10

my understanding about CHOOSE function that based on the number in the 1st argument, the function will choose the content in the corresponding argument placement afterwards.
 
Upvote 0
Looks like...

In E2 control+shift+enter, not just enter, and copy down:
Rich (BB code):

=IF(ISNUMBER(MATCH($B2&"|"&$C2,$B$1:B1&"|"&$C$1:C1,0)),"",
  VLOOKUP($B2&"|"&$C2,CHOOSE({1,2},$B$2:$B$10&"|"&$C$2:$C$10,$D$2:$D$10),2,0))

Aladin,

This is exactly what I was looking for!! I can't thank you enough! It will take me sometime to understand and figure out how this works. Thank you so much!
 
Upvote 0
Dr. Aladin

Your solution as always very fascinating for newbies like me :) took me an hour to crunch it.

one thing i still cannot comprehended is this:



when i analyze it in Evaluate formula option; i can understand it creates an array of two columns, but i cant get it its like magic..
how did the CHOOSE function interacted with this operator {} ? how did it understand that this operator means create an array of two columns one consist of the resultant of this $B$2:$B$10&"|"&$C$2:$C$10 and the other column consist of this $D$2:$D$10

my understanding about CHOOSE function that based on the number in the 1st argument, the function will choose the content in the corresponding argument placement afterwards.

When CHOOSE is fed with an array constant, i.e., {1,2}, this function will pick out the evaluations of the corresponding terms, and put them in an array.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top