Logical Functions

[Mark Watt]

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

 

[tactps]

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

 

[Mark Watt]

Thanks Tactps

Thank you for your reply.

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.

Thanks again for your help.

Mark

 

[Domenic]

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]

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.