Get max value

Closed
senthilrajagopi
Posts
4
Registration date
Tuesday January 6, 2015
Status
Member
Last seen
January 8, 2015
- Jan 7, 2015 at 01:11 AM
 RayH - Jan 9, 2015 at 10:20 AM
Col A Col B Col C
Row 1 A A 110
Row 2 A A 200
Row 3 C C 199
Row 4 A A 92
Row 5 C C 85
Row 6 D D 172
Row 7 D D 150
Row 8 E E 177
Row 9 E E 199
Row 10 E E 155


if column A & B are same, and the result in column D taking the max of respective values in column C.

Final result as below.

ColA ColB ColC Col D
Row 1 A A 110
Row 2 A A 200 200
Row 3 C C 199 199
Row 4 A A 92
Row 5 C C 85
Row 6 D D 172 172
Row 7 D D 150
Row 8 E E 177
Row 9 E E 199 199
Row 10 E E 155

Give the excel formula for this and no duplicate value in Column D.

Kindly do the needful

3 replies

Not exactly what you wanted but its close:

=IF(A2=0,0,MAX(IF(A2=$B$2:$B$11,$C$2:$C$11,0)))

This returns the MAX value for the pair.

Enter this formula as an Array formula.

So instead of pressing Enter, press Ctrl-Shift Enter.
This will put curly braces { and } around the formula. drag this formula down the list.
0
senthilrajagopi
Posts
4
Registration date
Tuesday January 6, 2015
Status
Member
Last seen
January 8, 2015

Jan 8, 2015 at 03:53 AM
Hi thanks

it's working. but i need one more requirement in this scenario.

if in two cells are equal, in column d duplicate values to be eliminated.
0
RayH > senthilrajagopi
Posts
4
Registration date
Tuesday January 6, 2015
Status
Member
Last seen
January 8, 2015

Jan 9, 2015 at 10:20 AM
That was the bit I had trouble with. Like I said it was close.
0
senthilrajagopi
Posts
4
Registration date
Tuesday January 6, 2015
Status
Member
Last seen
January 8, 2015

Jan 8, 2015 at 01:11 AM
thanks buddy, i will try and given the feedback ASAP
0
senthilrajagopi
Posts
4
Registration date
Tuesday January 6, 2015
Status
Member
Last seen
January 8, 2015

Jan 8, 2015 at 01:27 AM
it's working. but i need one more requirement in this scenario.

if in two cells are equal, in column d duplicate values to be eliminated.

kindly give the ASAP.
0