Find highest number in a specific group

[Robert305]

Hello, i'm trying to find the highest number in a specific group. Basically I have column A which is a period and column B which is a quarter. I want to only show the biggest quarter (B) of a period group (A) in C, kind of like this:

Any ideas how I could accomplish this? I'm completely stuck on this

Thanks,
Robert

 

[Special-K99]

in C1
=IF(B1=MAX(IF(A$1:A$1000=A1,B$1:B$1000)),MAX(IF(A$1:A$1000=A1,B$1:B$1000)),"")
Array formula, use Ctrl-Shift-Enter

and copy down

 

[Grummet]

Do you want it to look exactly like pictured, where the highest quarter is next to the answer?

Or will this be elsewhere in the sheet, for something like this:

Year	Highest Quarter
1	3
2	4
3	2
4	4
5	1
6	4
7	3

<tbody>
</tbody>

 

[Robert305]

in C1
=IF(B1=MAX(IF(A$1:A$1000=A1,B$1:B$1000)),MAX(IF(A$1:A$1000=A1,B$1:B$1000)),"")
Array formula, use Ctrl-Shift-Enter

and copy down

Wow thanks that did the trick! Only had to change the row since youre going with the first one and my data starts in row 2 but after that it worked!

 

[Robert305]

Ok I just realized this is a little more complicated because I also have groupings that need to be considered:

So basically each group in column A needs to be considered and the years in B keep repeating. Is this still doable?

 

[63falcondude]

Try
=IF(C2=MAX(IF($B$2:$B$27=B2,IF($A$2:$A$27=A2,$C$2:$C$27))),MAX(IF($B$2:$B$27=B2,IF($A$2:$A$27=A2,$C$2:$C$27))),"")

Sources: Special-K99 and https://exceljet.net/formula/maximum-if-multiple-criteria

 

[Robert305]

Try
=IF(C2=MAX(IF($B$2:$B$27=B2,IF($A$2:$A$27=A2,$C$2:$C$27))),MAX(IF($B$2:$B$27=B2,IF($A$2:$A$27=A2,$C$2:$C$27))),"")

Sources: Special-K99 and https://exceljet.net/formula/maximum-if-multiple-criteria

Thanks, worked perfectly!!!

 

[63falcondude]

Glad to help.