Return true/false for each row for the maximum of a subset data in an array

[dlortie]

Hi,

I have the following data where I need to return whether I am on the maximum row based on multiple criteria
I will have the formula in the formula column for each row in the example below with a TRUE or FALSE.
In the example below, I should see TRUE in Rows 2 and 15.

Basically for each unique identifier, I need to find the most recent start date and then for these most recent start dates, I need to find the row with the largest step.

I understand array formulas, but I cannot seem to get this to work - specifically for a subset of data.

RowId Identifier Start Date Step Formula
1 73643769 FALSE
2 73643769 2015-12-09 5 TRUE
3 73643769 2015-12-09 0 FALSE
4 73643769 2015-10-06 5 FALSE
5 73643769 2015-10-06 0 FALSE
6 73643769 2015-02-07 8 FALSE
7 73643769 2015-02-07 8 FALSE
8 73643769 2015-02-07 0 FALSE
9 73643769 2015-02-07 0 FALSE
10 73643769 8 FALSE
11 73643769 8 FALSE
12 73643769 8 FALSE
13 73643769 8 FALSE
14 12345678 2015-02-04 5 FALSE
15 12345678 2015-02-04 8 TRUE
16 12345678 2015-02-02 0 FALSE
17 12345678 8 FALSE
18 12345678 8 FALSE

 

[AhoyNC]

See if this might work for you.
Copy down as needed.

Excel Workbook
	A	B	C	D	E
1	RowId	Identifier	Start Date	Step	Formula
2	1	73643769			FALSE
3	2	73643769	12/9/2015	5	TRUE
4	3	73643769	12/9/2015	0	FALSE
5	4	73643769	10/6/2015	5	FALSE
6	5	73643769	10/6/2015	0	FALSE
7	6	73643769	2/7/2015	8	FALSE
8	7	73643769	2/7/2015	8	FALSE
9	8	73643769	2/7/2015	0	FALSE
10	9	73643769	2/7/2015	0	FALSE
11	10	73643769		8	FALSE
12	11	73643769		8	FALSE
13	12	73643769		8	FALSE
14	13	73643769		8	FALSE
15	14	12345678	2/4/2015	5	FALSE
16	15	12345678	2/4/2015	8	TRUE
17	16	12345678	2/2/2015	0	FALSE
18	17	12345678		8	FALSE
19	18	12345678		8	FALSE
Sheet

 

[Aladin Akyurek]

@AhoyNC

We can avoid two IF calls with an AND call...

{=AND(C2=MAX(IF($B$2:$B$19=B2,$C$2:$C$19)),D2=MAX(IF($C$2:$C$19=MAX(IF($B$2:$B$19=B2,$C$2:$C$19)),$D$2:$D$19)))}

If the range is huge, it might be wise to use the V() function to avoid computing the same expensive expression twice...

{=AND(C2=V(MAX(IF($B$2:$B$19=B2,$C$2:$C$19))),D2=MAX(IF($C$2:$C$19=V(),$D$2:$D$19)))}

For the latter to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

 

[dlortie]

Hi,

Thanks for the replies.

I tried the 1st answer which works very well except that I need to keep only one row for each unique identifier - the above will match many rows if they have the same max date and max step.

I tried the second answer with the UDF, but I can seem to get excel to recognize it....I get #NAME - I see ther UDF in the formula dialog box, but it seems to require the full worksheet name...I do have over 20k rows.

Thanks,
Daniel Lortie

 

[Aladin Akyurek]

Hi,

Thanks for the replies.

I tried the 1st answer which works very well except that I need to keep only one row for each unique identifier - the above will match many rows if they have the same max date and max step.

I tried the second answer with the UDF, but I can seem to get excel to recognize it....I get #NAME - I see ther UDF in the formula dialog box, but it seems to require the full worksheet name...I do have over 20k rows.

Thanks,
Daniel Lortie

Try to post a sample that illustrates the issue you seem to have. (By the way, the formulas are equivalent. That with V() requires adding V() to your workbook.)

 

[dlortie]

Here is an additional restriction which will allow me to return only one row per individual resource. I added the rate column where on top of the existing conditions, I need to only select the maximum salary for each unique Personnel number with maximum start date and maximum step (for the subset of maximum start date)

RowId	Personnel Number	Start Date	Step	Pay Rate	Formula		Formula Result (AND)	Formula Result (AND)+V()	Desired Result
1	12345678	16-Jan-16	4	95222			TRUE	TRUE	FALSE
2	12345678	16-Jan-16	4	112359			TRUE	TRUE	TRUE
3	12345678	31-Dec-15	8	105000			FALSE	FALSE	FALSE
4	12345678	31-Dec-15	5	95000			FALSE	FALSE	FALSE
5	12345678						FALSE	FALSE	FALSE
6	12345678						FALSE	FALSE	FALSE
7	12345678						FALSE	FALSE	FALSE
8	45678901	24-Dec-16	5	107000			FALSE	FALSE	FALSE
9	45678901	24-Dec-16	8	125000			TRUE	TRUE	TRUE
10	45678901	24-Dec-16	8	102000			TRUE	TRUE	FALSE
11	45678901	01-Jun-15	3	88000			FALSE	FALSE	FALSE
12	45678901	01-Jun-15	8	75000			FALSE	FALSE	FALSE
13	45678901						FALSE	FALSE	FALSE
14	45678901						FALSE	FALSE	FALSE

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>

<strike></strike>

Regards,
Daniel Lortie

 

[Aladin Akyurek]

No need to include the formula results in your exhibit... Note that none of the formulas wrong given the conditions you have stipulated at the outset. With the additional condition that you throw in...

Row\Col	A​	B​	C​	D​	E​	F​
1​	RowId	Personnel Number	Start Date	Step	Pay Rate	Formula
2​	1​	12345678​	16-Jan-16​	4​	95222​	FALSE
3​	2​	12345678​	16-Jan-16​	4​	112359​	TRUE
4​	3​	12345678​	31-Dec-15​	8​	105000​	FALSE
5​	4​	12345678​	31-Dec-15​	5​	95000​	FALSE
6​	5​	12345678​				FALSE
7​	6​	12345678​				FALSE
8​	7​	12345678​				FALSE
9​	8​	45678901​	24-Dec-16​	5​	107000​	FALSE
10​	9​	45678901​	24-Dec-16​	8​	125000​	TRUE
11​	10​	45678901​	24-Dec-16​	8​	102000​	FALSE
12​	11​	45678901​	1-Jun-15​	3​	88000​	FALSE
13​	12​	45678901​	1-Jun-15​	8​	75000​	FALSE
14​	13​	45678901​				FALSE
15​	14​	45678901​				FALSE

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

=AND(
    C2=MAX(IF($B$2:$B$15=B2,$C$2:$C$15)),
    D2=MAX(IF($C$2:$C$15=MAX(IF($B$2:$B$15=B2,$C$2:$C$15)),$D$2:$D$15)),
    E2=MAX(IF($D$2:$D$15=MAX(IF($C$2:$C$15=MAX(IF($B$2:$B$15=B2,$C$2:$C$15)),
      $D$2:$D$15)),$E$2:$E$15)))

With V(), we get:

<strike></strike>=AND(
    C2=V(MAX(IF($B$2:$B$15=B2,$C$2:$C$15))),
    D2=MAX(IF($C$2:$C$15=V(),$D$2:$D$15)),
    E2=MAX(IF($D$2:$D$15=MAX(IF($C$2:$C$15=V(),$D$2:$D$15)),$E$2:$E$15)))<strike></strike>

 

[dlortie]

Hi,

I have to figure out why the function worked in my Excel 2016 at home and returns #NAME in my Excel 2010 at work. I can confirm that the V() function is in the workbook code and I can see it in the formula dialogue.

...Yes I added a salary column to get unique rows...I understand that every answer provided meets my initial requirements as well as the additional requirement for unique rows.

Thanks for all of your help!

Daniel

 

[Aladin Akyurek]

Hi,

I have to figure out why the function worked in my Excel 2016 at home and returns #NAME in my Excel 2010 at work. I can confirm that the V() function is in the workbook code and I can see it in the formula dialogue.

Should work in Excel 2000 too. Make sure that it's added as a module.

...Yes I added a salary column to get unique rows...I understand that every answer provided meets my initial requirements as well as the additional requirement for unique rows.

Thanks for all of your help!

Daniel

You are welcome.

 

[dlortie]

I have an issue with the last answer that was provided that took into account the pay rate column for uniqueness.

On Row 2, I am expecting this to return TRUE as it represents for Personnel number 73643769 the last pay action (max date: 09-Dec-15) as well as the max step for that max date (step 5 for date 09-Dec-15 for Personnel 73643769). When I step through/evaluate the formula, it seems to ignore the Personnel restriction and returns the max salary of 118,666.00 (for a different Personnel Number)

For example in E3 => Stepping through the formula the last part of the formula does not seem to restrict on the Personnel number or the largest date and tries to match the largest pay for a step of 5

E3 Partial eval
AND(TRUE, TRUE, 102059=MAX(IF({-1;5;0;5;0;8;8;0;0;8;5;-1;8;0;5;0;8}=5,E$2:E$18)))

AND(TRUE, TRUE, 102059=MAX(IF({FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},E$2:E$18)))

AND(TRUE, TRUE, 102059=118666)

AND(TRUE, TRUE, FALSE)

RowId Personnel number Start Date Step Pay Rate Formula Comment
1 73643769 -1 FALSE
2 73643769 09-Dec-15 5 102,059.00 FALSE Expecting True
3 73643769 09-Dec-15 0 0 FALSE
4 73643769 06-Oct-15 5 102,059.00 FALSE
5 73643769 2015-10-06 0 0 FALSE
6 73643769 2015-02-07 8 97,322.00 FALSE
7 73643769 2015-02-07 8 97,322.00 FALSE
8 73643769 07-Feb-15 0 0 FALSE
9 73643769 07-Feb-15 0 0 FALSE
10 73643769 8 0 FALSE
11 73643769 5 0 FALSE
12 32837275 -1 FALSE
13 32837275 2015-04-25 8 111,639.00 TRUE
14 32837275 2015-04-25 0 0 FALSE
15 32837275 2014-06-02 5 118,666.00 FALSE
16 32837275 2014-06-02 0 0 FALSE
17 32837275 8 0 FALSE