Search criteria in one column and find maximum corresponding value in another column, then show maximum value in every row that matches original crite

[Josh_Hawthorn]

Hi.

I would really appreciate some help with the below.

Column A is the order number, B is the status of the indvidual line, C reflects B numerically with a "1" for complete and "0" for incomplete.

If A1 = A2 then D is blank, if there is a difference then the completion % is displayed.

I would like column E to show the max value of column D for each value shown in column A.

For example E1:E3 would each show a value of 33%.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Incomplete[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Incomplete[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Complete[/TD]
[TD]1[/TD]
[TD]33%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Complete[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Incomplete[/TD]
[TD]0[/TD]
[TD]50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Complete[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Complete[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Complete[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Complete[/TD]
[TD]1[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help with this would be amazing!! Thanks!

 

[istiasztalos]

Hi Josh,

You could try this one,

There's no 'MAXIF' formula by default in Excel, an array formula can circumvent this, you'll need to add this using CTRL+SHIFT+ENTER instead of just ENTER (Excel will add {} around it)

[TABLE="class: grid, width: 369"]
<tbody>[TR]
[TD="align: right"]A[/TD]
[TD]B[/TD]
[TD="align: right"]C[/TD]
[TD]D[/TD]
[TD="align: right"]E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Incomplete[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"][TABLE="width: 432"]
<tbody>[TR]
[TD="width: 432"]=MAX(IF(A$1:A$9=A1,D$1:D$9))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Incomplete[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]33%[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Complete[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]33%[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Complete[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Incomplete[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Complete[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Complete[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Complete[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Complete[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]100%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Josh_Hawthorn]

Hi Josh,You could try this one,There's no 'MAXIF' formula by default in Excel, an array formula can circumvent this, you'll need to add this using CTRL+SHIFT+ENTER instead of just ENTER (Excel will add {} around it)[TABLE="class: grid, width: 369"]<tbody>[TR][TD="align: right"]A[/TD][TD]B[/TD][TD="align: right"]C[/TD][TD]D[/TD][TD="align: right"]E[/TD][/TR][TR][TD="align: right"]1[/TD][TD]Incomplete[/TD][TD="align: right"]0[/TD][TD][/TD][TD="align: right"][TABLE="width: 432"]<tbody>[TR] [TD="width: 432"]=MAX(IF(A$1:A$9=A1,D$1:D$9))[/TD][/TR]</tbody>[/TABLE][/TD][/TR][TR][TD="align: right"]1[/TD][TD]Incomplete[/TD][TD="align: right"]0[/TD][TD][/TD][TD="align: right"]33%[/TD][/TR][TR][TD="align: right"]1[/TD][TD]Complete[/TD][TD="align: right"]1[/TD][TD="align: right"]33%[/TD][TD="align: right"]33%[/TD][/TR][TR][TD="align: right"]2[/TD][TD]Complete[/TD][TD="align: right"]1[/TD][TD][/TD][TD="align: right"]50%[/TD][/TR][TR][TD="align: right"]2[/TD][TD]Incomplete[/TD][TD="align: right"]0[/TD][TD="align: right"]50%[/TD][TD="align: right"]50%[/TD][/TR][TR][TD="align: right"]3[/TD][TD]Complete[/TD][TD="align: right"]1[/TD][TD][/TD][TD="align: right"]100%[/TD][/TR][TR][TD="align: right"]3[/TD][TD]Complete[/TD][TD="align: right"]1[/TD][TD][/TD][TD="align: right"]100%[/TD][/TR][TR][TD="align: right"]3[/TD][TD]Complete[/TD][TD="align: right"]1[/TD][TD][/TD][TD="align: right"]100%[/TD][/TR][TR][TD="align: right"]3[/TD][TD]Complete[/TD][TD="align: right"]1[/TD][TD="align: center"]100%[/TD][TD="align: right"]100%[/TD][/TR]</tbody>[/TABLE]

This works perfectly!!

 

[Peter_SSs]

There's no 'MAXIF' formula by default in Excel,

There is in Excel 2016. (It's actually MAXIFS but will do the same job)


@Josh
Whilst you have a perfectly good answer, here are 3 more, none of which requires the Ctrl+Shift+Enter confirmation but the MAXIFS does require Excel 2016 and the AGGREGATE requires Excel 2010 or later.
The last one is the simplest but does require the column to be filled down before each one shows the correct result.

Book1
	A	B	C	D	E	F	G
1
2	1	Incomplete	0		33%	33%	33%
3	1	Incomplete	0		33%	33%	33%
4	1	Complete	1	33%	33%	33%	33%
5	2	Complete	1		50%	50%	50%
6	2	Incomplete	0	50%	50%	50%	50%
7	3	Complete	1		100%	100%	100%
8	3	Complete	1		100%	100%	100%
9	3	Complete	1		100%	100%	100%
10	3	Complete	1	100%	100%	100%	100%
Max per Order

Cell Formulas
Range	Formula
E2	=MAXIFS(D$2:D$10,A$2:A$10,A2)
F2	=AGGREGATE(14,6,D$2:D$10/(A$2:A$10=A2),1)
G2	=IF(D2,D2,G3)