If multiple duplicate values, then return max value from third cell

[saintbernard]

Hi,
I have an excel table with 20000 lines. Here is a short sample it, with license plate number (column B) which last process phase they have gone through. There are some duplicate values in the plate numbers, which have been indicated in column C. In column D, I would like to return for each line, what the highest process phase was of that plate number.
Can anyone help with a formula?

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]A
Last process phase[/TD]
[TD]B
Plate Number[/TD]
[TD]C
Duplicate or original[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1ABC123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1ABC123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1ABC123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ZZZ123[/TD]
[TD]Original[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1CBA321[/TD]
[TD]Original[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1HIJ456[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1HIJ456[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1HIJ456[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AAA444[/TD]
[TD]Original[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi and welcome to MrExcel,

take a look at this:

Book1
	A	B	C	D
1	Last process phase	Plate Number	Duplicate or original
2	1	1ABC123	Duplicate	2
3	1	1ABC123	Duplicate	2
4	2	1ABC123	Duplicate	2
5	2	ZZZ123	Original	2
6	3	1CBA321	Original	3
7	1	1HIJ456	Duplicate	4
8	1	1HIJ456	Duplicate	4
9	4	1HIJ456	Duplicate	4
10	3	AAA444	Original	3
Sheet1

Cell Formulas
Range	Formula
D2	{=MAX(IF($B$2:$B$10=B2,$A$2:$A$10))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[saintbernard]

Hi and welcome to MrExcel,

take a look at this:

	A	B	C	D
Last process phase	Plate Number	Duplicate or original
1ABC123	Duplicate
1ABC123	Duplicate
1ABC123	Duplicate
ZZZ123	Original
1CBA321	Original
1HIJ456	Duplicate
1HIJ456	Duplicate
1HIJ456	Duplicate
AAA444	Original

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=MAX(IF($B$2:$B$10=B2,$A$2:$A$10))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thanks, but it doesn't work, I get 4 for each row.
I want in each row, the highest process phase per plate number.

 

[jorismoerings]

Did you notice this remark:

Entered with Ctrl+Shift+Enter.
If entered correctly, Excel will surround with curly braces {}.

 

[JackDanIce]

Adjust range inputs to suit, non-CSE/'normal' formula, try:

=MAX(INDEX(($B$1:$B$200=B1)*$A$1:$A$200,0))

 

[saintbernard]

Thanks it worked!

 

[JackDanIce]

You're welcome