# Not your typical "return max value"

#### jlove

##### New Member
I have a list of numbers where the first 9 digits often repeat, while the final 2 digits often differ. I want to automatically boil this down to: Where there is duplication among the first 9 digits, return the cell with the max value in the final 2 digits. If there is no duplication, simply return the value of the cell. The example below illustrates example input (Item Number) and expected output (Max_Item Number)

Item Number Max_Item Number
840-00363-02 840-00363-02
840-00363-00 840-00362-02
840-00362-02 840-00361-01
840-00362-01 840-00343-01
840-00362-00 840-00308-01
840-00361-01 840-00303-01
840-00361-00 840-00301-00
840-00343-01
840-00343-00
840-00308-01
840-00308-00
840-00303-01
840-00303-00
840-00301-00

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Assuming no duplicates, for instance, 840-00363-02 mentioned twice, then use the below which assumes numbers start in cell A2. If done correctly, the results should appear like below. Reach out with any questions.

B2: Ctrl+Shift+Enter
Code:
``{=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}``

C2: Just Enter
Code:
``=IF(ROWS(\$C\$2:C2)>MAX(\$B\$2:\$B\$15),"",INDEX(\$A\$2:\$A\$15,MATCH(ROWS(\$C\$2:C2),\$B\$2:\$B\$15,0)))``

 Item Number Number Extraction Max List 840-00363-02 1 840-00363-02 840-00363-00 1 840-00362-02 840-00362-02 2 840-00361-01 840-00362-01 2 840-00343-01 840-00362-00 2 840-00308-01 840-00361-01 3 840-00303-01 840-00361-00 3 840-00301-00 840-00343-01 4 840-00343-00 4 840-00308-01 5 840-00308-00 5 840-00303-01 6 840-00303-00 6 840-00301-00 7

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Last edited:
My old nemesis has returned - displaying the formula ( B2 array) rather than the result. Yes I have checked advanced options and yes I (kind of) tried to set format general, delete the =, enter, put it back. That kind of doesn't work with an array?

Item_Number Extract
840-00363-02 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00363-00 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00362-02 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00362-01 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00362-00 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00361-01 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00361-00 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00343-01 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00343-00 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00308-01 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00308-00 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00303-01 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00303-00 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}
840-00301-00 {=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)}

don't copy the bracketed part of the formula... the brackets will appear when you enter the formula with Ctrl+Shift+Enter... Copy the below formula instead and then enter with Ctrl+Shift+Enter. Reach out with further issues/questions.

B2:
Code:
``[COLOR=#333333]=IF(LARGE((LEFT(\$A\$2:\$A\$15,9)=LEFT(\$A2,9))*(SUBSTITUTE(\$A\$2:\$A\$15,"-","")),1)=--SUBSTITUTE(\$A2,"-",""),SUM(B1,1),B1)[/COLOR]``

Last edited:
Brilliant! Thank you so much, this is very useful!

You're welcome.

Replies
5
Views
2K
Replies
8
Views
801
Replies
7
Views
484
Replies
4
Views
467
Replies
11
Views
2K

1,196,120
Messages
6,013,578
Members
441,774
Latest member
esandoval

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