Returning results based on multiple criteria

[Nlambert]

I am working on a sheet that returns a value if a certain number of criteria have been met. I can't upload a spreadsheet at work but hopefully I can explain the situation accordingly in plain english.

All are cell 5 through cell 99.

Column B = Employee Name - there are names repeated if the employee is supporting tasks for multiple companies
Column C = Company
Column E = Task end date
Column F = Remaining hours to charge
Column G = Level of effort - LOE (some employees may spend 20% of their time for Company A, 30% for company B, etc...)
Column H - Rank (This column ranks the lines based upon when the end date occurs on each task by employee. The closest end date is ranked 1, second closest 2, etc...)

The purpose of the formula is to look at each employee name & company name, and return the LOE % for the earliest finish date for each employee by company. So if an employee has two finish dates for two companies that occur before the other companies I want it to look up the employee LOE% from the LOE tab and return the value that corresponds to each employee and company. Basically I need to be able to determine which companies each employee needs to support first and ignore the ones with a finish date down the road.

As an example:
---------A--------------B--------------E-----------G------------H
----Employee-----Company-----End date-----LOE %------Rank

5-----Dan-----------Micro Corp----4/25/17------50%---------1
6-----Dan-----------Mid Corp------6/15/17------50%---------3
7-----Dan-----------Macro Corp---4/25/17------50%---------2
8-----Dan-----------Over Corp----7/1/17--------50%---------4

Since Micro Corp and Macro Corp end first, Dan should be devoting his time 50/50 between the two companies and ignoring Mid Corp and Over Corp until the first two tasks are complete. So I want column G to return a blank value until he finishes his first two assignments.

My formula right now is:

{=IF($A$5:$A$99=A5,IF($B$5:$B$99=B5,IF(H5=1,INDEX(LOE!D:D,MATCH(A5&B5,LOE!$B$2:$B$200&LOE!$C$2:$C$200,0)),"")))))}

I am returning a result of 100% for column G5, and I assume it is because one of my criteria is that H5=1.

I am not sure if this is the correct formula to use as I really need the rank formula to not only rank by finish date, but also by company as well. So even if Macro Corp is ranked #2 by date, that is the earliest job due to Macro Corp, so it should also be ranked #1 since the employee has to work both.

I hope this isn't too confusing to understand but I would really appreciate the help. I think I may be overcomplicating this.

Here is my rank formula (thanks to another forum member!):

{=RANK.EQ(E5,INDEX($E$5:$E$200,SMALL(IF($B$5:$B$200=B5,ROW($E$5:$E$200)-ROW($E$5)+1),1)):INDEX($E$5:$E$200,LARGE(IF($B$5:$B$200=B5,ROW($E$5:$E$200)-ROW($E$5)+1),1)),1)}

 

[Snakehips]

Nlambert,

Not sure Iv'e got the full picture but...

Excel Workbook
	B	C	D
2	John	Micro Corp	10%
3	John	Mid Corp	20%
4	John	Macro Corp	30%
5	John	Over Corp	40%
6	Dan	Mid Corp	35%
7	Dan	Over Corp	40%
8	Dan	Micro Corp	50%
9	Dan	Other Corp	60%
10	Dan	Macro Corp	50%
LOE

Excel Workbook
	A	B	C	D	E	F	G	H
4	*	*	*	*	Date	*	LOE%	Ranking
5	Dan	*	Micro Corp	*	25/04/2017	*	50%	1
6	Dan	*	Mid Corp	*	15/06/2017	*	*	3
7	Dan	*	Macro Corp	*	25/04/2017	*	50%	1
8	Dan	*	Over Corp	*	01/07/2017	*	*	4
Sheet1

Hope that helps

 

[Nlambert]

Nlambert,

Not sure Iv'e got the full picture but...

LOE

*	B	C	D
2	John	Micro Corp	10%
3	John	Mid Corp	20%
4	John	Macro Corp	30%
5	John	Over Corp	40%
6	Dan	Mid Corp	35%
7	Dan	Over Corp	40%
8	Dan	Micro Corp	50%
9	Dan	Other Corp	60%
10	Dan	Macro Corp	50%

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 90px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sheet1

*	A	B	C	D	E	F	G	H
4	*	*	*	*	Date	*	LOE%	Ranking
5	Dan	*	Micro Corp	*	25/04/2017	*	50%	1
6	Dan	*	Mid Corp	*	15/06/2017	*	*	3
7	Dan	*	Macro Corp	*	25/04/2017	*	50%	1
8	Dan	*	Over Corp	*	01/07/2017	*	*	4

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 36px;"><col style="width: 87px;"><col style="width: 36px;"><col style="width: 95px;"><col style="width: 35px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula G5 {=IF(H5=1,MAX((LOE!$B$2:$B$200=A5)*(LOE!$C$2:$C$200=C5)*(LOE!$D$2:$D$200)),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Hope that helps

Tony, you almost have exactly what I need posted except that on my spreadsheet Dan doesn't have two companies ranked as #1. Since there is a tie in the ranking due to both companies having an end date of the same day, one of the companies (let's say Macro Corp) is ranked as #2. Since it is #2 the formula ignores the ranking and doesn't assign an LOE.

Is there a way to write the rank formula to call out a rank of #1 for each company with the criteria below?

I need it to find the soonest date for each company that Dan works with, and return the LOE percentage for all of them that Dan needs to support with a rank of 1. As it stands now, the first company with the soonest date will return a rank of 1, and any other companies (regardless of their due date) will return a 2, and thus the formula ignores it.

Here is my rank formula:

{=RANK.EQ(E5,INDEX($E$5:$E$3000,SMALL(IF($A$5:$A$200=A5,ROW($E$5:$E$200)-ROW($E$5)+1),1)):INDEX($E$5:$E$3000,LARGE(IF($A$5:$A$3000=A5,ROW($E$5:$E$3000)-ROW($E$5)+1),1)),1)}

 

[Snakehips]

? your Formula returns ranking 1,3,1,4 for me ! Similar if you just use RANK rather than RANK.EQ

Are your dates pure date or do you have an unseen time element in there? put =E7-E5 in a spare cell and see if the answer is zero.

 

[Nlambert]

I gave that a whirl, and my results were 0. I retyped the rank formula and I now have multiple 1 rankings for the employee, however it is showing me multiple 1s for the same customer.

One customer is listed twice, but one of the criteria is that if the remaining hours are 0 regardless of the date, that date is to be ignored since the work is done and the next lowest date should be ranked instead. It is not ignoring the field, but instead only looking at the date and as such returns a value of 1.

Column F has the remaining hours for each task for each customer. I think this may need to be folded in.

My criteria for the rank formula needs to be:

If Employee name in column A = "XYZ"
and if remaining hours is <> 0 in column F
then rank from smallest to largest based upon the finish date
If remaining hours is 0, then leave the cell blank

Once the rank is determined in column H, my criteria for the LOE formula in column G has to be:

If Employee name in column A = "XYZ"
and if company name in column B = "ABC"
and rank = "1", then match the employee name and customer to the LOE tab and return the LOE % from the LOE tab. Otherwise, leave the cell blank.