Not your typical "return max value"

[jlove]

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

 

[CyrusTheVirus]

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

{=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

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

 

[jlove]

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)}

 

[CyrusTheVirus]

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:

[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]

 

[jlove]

Brilliant! Thank you so much, this is very useful!

 

[CyrusTheVirus]

You're welcome.