How to find duplicate value in column and return value ???

[coolasia]

Dear Sir

I am trying to search for same value in a column and return value base on some formula

How to create a excel formulate that it will search in the column and if there is matching
fruit, it will generate a formula to add the price andr select the biggest number in the WEEK?

For example

Raw data below

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Week[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]10[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]11[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]11[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]12[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]12[/TD]
[TD]0.3[/TD]
[/TR]
</tbody>[/TABLE]


----------------------------------------------------------------

Reuslt will return below

1) Final Week formula should search for similar fruit and return the highest number
2) Total price formula should search for similar fruit and add all the price together

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Week[/TD]
[TD]Price[/TD]
[TD]Final week[/TD]
[TD]Total Price[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]0.2[/TD]
[TD]12[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]10[/TD]
[TD]0.1[/TD]
[TD]11[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]11[/TD]
[TD]0.3[/TD]
[TD]12[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]11[/TD]
[TD]0.5[/TD]
[TD]11[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]12[/TD]
[TD]0.4[/TD]
[TD]12[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]12[/TD]
[TD]0.3[/TD]
[TD]12[/TD]
[TD]0.3[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for your advise

regards
Ken

 

[Aladin Akyurek]

In D2 control+shift+enter, not just enter, and copy down:

=INDEX($B$2:$B$7,MATCH(MAX(IF($A$2:$A$7=$A2,$C$2:$C$7)),IF($A$2:$A$7=$A2,$C$2:$C$7),0))

In E2 just enter and copy down:

=SUMIFS($C$2:$C$7,$A$2:$A$7,$A2)

 

[coolasia]

In D2 control+shift+enter, not just enter, and copy down:

=INDEX($B$2:$B$7,MATCH(MAX(IF($A$2:$A$7=$A2,$C$2:$C$7)),IF($A$2:$A$7=$A2,$C$2:$C$7),0))

In E2 just enter and copy down:

=SUMIFS($C$2:$C$7,$A$2:$A$7,$A2)

Hi Aladin

Thanks for your advise

The problem is I am not familar with control+shift+enter. Should I copy your formula first and
enter CSE ? or I should copy and paste your formula first and enter CSE ?

I have try different way but my excel 2010 dont seen to work with CSE

Kindly advise

Thanks
Ken

 

[Aladin Akyurek]

1. Copy into or type the formula in the target cell.

2. Stay in that cell or go back to that cell.

3. Press down the control and the shift keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

4. Now you can copy down the formula.

In E2 you can replace the SUMIFS formula with one with the SUMIF function:

=SUMIF($A$2:$A$7,$A2,$C$2:$C$7)