Finding MAX number based on 2 criteria in a series without helper column...

[seaottr]

Hi all,

With help from the internet, I was able to find out the first and last test attempt for a user using an array formula, but I'm having trouble adapting it to look at TWO criteria (Name and Course). Because it is only currently looking at name, users that attempt Course 2 more times than Course 1, won't show a last attempt for Course 1 (and vice versa).


I'm hoping you can help me adapt the following array formula to look at both Name and Course to find out if a cell's value is the MAX in the series (preferably WITHOUT a helper column).

Current Formula (in F2):
{=IF(E2=1,"First", IF(E2=MAX(IF(A:A=A2,E:E)),"Last",""))}

Sample Table:
[TABLE="width: 831"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Course[/TD]
[TD]Question #[/TD]
[TD]Correct?[/TD]
[TD]Attempt #[/TD]
[TD]First or Last Attempt?[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]3[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD]<-- This should be last attempt for Course 1, but is taking last attempt for Course 2 because it's only comparing against Name, not Name AND Course[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD]<-- This should be last attempt for Course 1, but is taking last attempt for Course 2 because it's only comparing against Name, not Name AND Course[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 1[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD]<-- This should be last attempt for Course 1, but is taking last attempt for Course 2 because it's only comparing against Name, not Name AND Course[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 1[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 1[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 1[/TD]
[TD]3[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 1[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]Last[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 1[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]Last[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 1[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]Last[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]3[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]4[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]4[/TD]
[TD]N[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]4[/TD]
[TD]N[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]4[/TD]
[TD]Last[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]4[/TD]
[TD]Last[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]4[/TD]
[TD]Last[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Course 2[/TD]
[TD]4[/TD]
[TD]Y[/TD]
[TD]4[/TD]
[TD]Last[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 2[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 2[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 2[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Course 2[/TD]
[TD]4[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]First[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col><col><col><col></colgroup>[/TABLE]

 

[steve the fish]

You shouldnt use full column references as they will be slow in array formulas. You need to nest in an additional IF.

=IF(E2=1,"First", IF(E2=MAX(IF($A$2:$A$1000=A2,IF($B$2:$B$1000=B2,$E$2:$E$1000))),"Last",""))

 

[seaottr]

Thanks so much for the solution Steve! This worked and it looks like it's something I should have been able to solve myself, but totally couldn't...LOL! I understand the recommendation to not use full column references in array formulas. There are thousands of rows that get added weekly, but I'm actually using tables, so it's just using the table columns, not full worksheet columns for the formulas. I didn't use a table for the example just to make it simpler to read

Thanks again!