# Logical Functions

#### Mark Watt

##### New Member
Greetings,

I am trying to set up a formula that will check a list for duplicate entries then report back the highest number (or highest tied number) associated with the first column. For example, Column A would have 'Apples' 'Bananas' 'Apples' 'Bananas' 'Apples' 'Apples'. Column B would read '10' '4' 8' '7' '10' '9'. And Column C would have bin numbers 'Bin 1' 'Bin 2' 'Bin3' 'Bin 4' 'Bin5'.

I can Sort the list to separate Apples and Bananas, but would like to create a formula that would show me where to find the 'most' apples (or two places). I have tried using the vlookup command in combination with the large command but I can't figure out what to do in the occurance of a tie (for the highest value only)

Thank you for any assistance,
Mark

*Edit* Posted Screenshot for further clarity (Or not)

[HTML removed]

Edited by Von Pookie

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
This will find one of them for you (the largest number bin in the event of a tie):

=INDIRECT("C"&MATCH((MAX((A1:A6="Apples")*B1:B6*VALUE(RIGHT(C1:C6,1))))/MAX((A1:A6="Apples")*VALUE(RIGHT(C1:C6,1))),B:B))

Enter with shift-Ctrl-Enter

Thanks Tactps

I was hoping to post the formulas I used to help answer this question for future reference but I left the spreadsheet at work. However the logic I used was a series of IF commands comparing the cell values between the cell in question to the cell above and below. If the cell is unique between the three cells it returns the bin value. If it is duplicated then I created another formula to look at the associated inventory, if that inventory is the highest number to report this Bin number. My final formula compares tied inventory amounts (with the same stock name) to evaluate whether or not the tied amounts are the highest numbers. Kind of a long way to do things but just not all that experienced with nesting formulas.

Mark

Assuming that A2:C7 contains your data, try the following which will take ties into consideration...

G1: enter the fruit of interest, such as Apples

D2, copied down:

=IF(A2=\$G\$1,(SUMPRODUCT(--(\$A\$2:\$A\$7=\$G\$1),--(B2<\$B\$2:\$B\$7))+1)+COUNTIF(\$B\$2:B2,B2)-1,"")

E1: enter 1, indicating that you want to find the bin or bins with the most fruit, for the fruit entered in G1, such as Apples

F1:

=MAX(IF((A2:A7=G1)*(B2:B7=INDEX(B2:B7,MATCH(1,(A2:A7=G1)*(D2:D7=E1),0))),D2:D7))-E1

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

H1:

=MAX(IF(A2:A7=G1,B2:B7))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

H2, copied down:

=IF(ROWS(H\$2:H2)<=\$E\$1+\$F\$1,INDEX(C\$2:C\$7,MATCH(ROWS(H\$2:H2),\$D\$2:\$D\$7,0)),"")

Hope this helps!

Domenic said:
Assuming that A2:C7 contains your data, try the following which will take ties into consideration...

G1: enter the fruit of interest, such as Apples

D2, copied down:

=IF(A2=\$G\$1,(SUMPRODUCT(--(\$A\$2:\$A\$7=\$G\$1),--(B2<\$B\$2:\$B\$7))+1)+COUNTIF(\$B\$2:B2,B2)-1,"")

E1: enter 1, indicating that you want to find the bin or bins with the most fruit, for the fruit entered in G1, such as Apples

F1:

=MAX(IF((A2:A7=G1)*(B2:B7=INDEX(B2:B7,MATCH(1,(A2:A7=G1)*(D2:D7=E1),0))),D2:D7))-E1

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

H1:

=MAX(IF(A2:A7=G1,B2:B7))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

H2, copied down:

=IF(ROWS(H\$2:H2)<=\$E\$1+\$F\$1,INDEX(C\$2:C\$7,MATCH(ROWS(H\$2:H2),\$D\$2:\$D\$7,0)),"")

Hope this helps!

Correction...

D2, copied down:

=IF(A2=\$G\$1,(SUMPRODUCT(--(\$A\$2:\$A\$7=A2),--(B2<\$B\$2:\$B\$7))+1)+(SUMPRODUCT(--(\$A\$2:A2=A2),--(\$B\$2:B2=B2))-1),"")

F1:

=MAX(IF((A2:A7=G1)*(B2:B7=INDEX(B2:B7,MATCH(E1,D2:D7,0))),D2:D7))-E1

...confirmed with CONTROL+SHIFT+ENTER.

Replies
6
Views
205
Replies
11
Views
527
Replies
3
Views
305
Replies
11
Views
471
Replies
4
Views
807

### Forum statistics

1,207,402
Messages
6,078,270
Members
446,324
Latest member
JKamlet ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back