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

#### coolasia

##### New Member
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

 Fruit Week Price Apple 10 0.2 Orange 10 0.1 Apple 11 0.3 Orange 11 0.5 Apple 12 0.4 Banana 12 0.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

 Fruit Week Price Final week Total Price Apple 10 0.2 12 0.9 Orange 10 0.1 11 0.6 Apple 11 0.3 12 0.9 Orange 11 0.5 11 0.6 Apple 12 0.4 12 0.9 Banana 12 0.3 12 0.3

<tbody>
</tbody>

regards
Ken

### 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.

##### MrExcel MVP
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
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)

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

Thanks
Ken

##### MrExcel MVP
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:

Replies
10
Views
102
Replies
6
Views
92
Replies
5
Views
61
Replies
3
Views
373
Replies
11
Views
552