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

coolasia

New Member
Joined
May 10, 2018
Messages
2
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

FruitWeekPrice
Apple100.2
Orange100.1
Apple110.3
Orange110.5
Apple120.4
Banana120.3

<tbody>
</tbody>


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

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

FruitWeekPriceFinal weekTotal Price
Apple100.2120.9
Orange100.1110.6
Apple110.3120.9
Orange110.5110.6
Apple120.4120.9
Banana120.3120.3

<tbody>
</tbody>


Thanks in advance for your advise

regards
Ken
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
May 10, 2018
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,113,876
Messages
5,544,826
Members
410,637
Latest member
hulse509
Top