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

 

[Aladin Akyurek]

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

The formula produces the results you have asked for, unless the above exhibit is yet another sample...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td] RowId[/td][td] Personnel Number[/td][td] Start Date[/td][td] Step[/td][td] Pay Rate[/td][td] Formula[/td][td] Desired Result[/td][/tr]


[tr][td]

2​

[/td][td]

1​

[/td][td]

12345678​

[/td][td]

16-Jan-16​

[/td][td]

4​

[/td][td]

95222​

[/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

3​

[/td][td]

2​

[/td][td]

12345678​

[/td][td]

16-Jan-16​

[/td][td]

4​

[/td][td]

112359​

[/td][td] TRUE[/td][td]

TRUE​

[/td][/tr]


[tr][td]

4​

[/td][td]

3​

[/td][td]

12345678​

[/td][td]

31-Dec-15​

[/td][td]

8​

[/td][td]

105000​

[/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

5​

[/td][td]

4​

[/td][td]

12345678​

[/td][td]

31-Dec-15​

[/td][td]

5​

[/td][td]

95000​

[/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

6​

[/td][td]

5​

[/td][td]

12345678​

[/td][td] [/td][td] [/td][td] [/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

7​

[/td][td]

6​

[/td][td]

12345678​

[/td][td] [/td][td] [/td][td] [/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

8​

[/td][td]

7​

[/td][td]

12345678​

[/td][td] [/td][td] [/td][td] [/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

9​

[/td][td]

8​

[/td][td]

45678901​

[/td][td]

24-Dec-16​

[/td][td]

5​

[/td][td]

107000​

[/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

10​

[/td][td]

9​

[/td][td]

45678901​

[/td][td]

24-Dec-16​

[/td][td]

8​

[/td][td]

125000​

[/td][td] TRUE[/td][td]

TRUE​

[/td][/tr]


[tr][td]

11​

[/td][td]

10​

[/td][td]

45678901​

[/td][td]

24-Dec-16​

[/td][td]

8​

[/td][td]

102000​

[/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

12​

[/td][td]

11​

[/td][td]

45678901​

[/td][td]

1-Jun-15​

[/td][td]

3​

[/td][td]

88000​

[/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

13​

[/td][td]

12​

[/td][td]

45678901​

[/td][td]

1-Jun-15​

[/td][td]

8​

[/td][td]

75000​

[/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

14​

[/td][td]

13​

[/td][td]

45678901​

[/td][td] [/td][td] [/td][td] [/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]


[tr][td]

15​

[/td][td]

14​

[/td][td]

45678901​

[/td][td] [/td][td] [/td][td] [/td][td] FALSE[/td][td]

FALSE​

[/td][/tr]
[/table]

The formula in F2 is:

=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)))<strike></strike>

which must be confirmed with control+shift+enter, not just enter, and copied down.

 

[dlortie]

Hi,

It works for the example subset but it does not for other data (same columns just a different subset of the more than 15k rows).

For example, just change the step values to 8 in the above subset of data and where I expect TRUE on row 3 (RowId = 2), It now returns to FALSE.

Regards,
Daniel

 

[Aladin Akyurek]

Hi,

It works for the example subset but it does not for other data (same columns just a different subset of the more than 15k rows).

For example, just change the step values to 8 in the above subset of data and where I expect TRUE on row 3 (RowId = 2), It now returns to FALSE.

Regards,
Daniel

Try to come up with a representative sample along with the correctly stipulated results that must obtain.