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

[TABLE="width: 374"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Item Number[/TD]
[TD]Number Extraction[/TD]
[TD]Max List[/TD]
[/TR]
[TR]
[TD]840-00363-02[/TD]
[TD="align: right"]1[/TD]
[TD]840-00363-02[/TD]
[/TR]
[TR]
[TD]840-00363-00[/TD]
[TD="align: right"]1[/TD]
[TD]840-00362-02[/TD]
[/TR]
[TR]
[TD]840-00362-02[/TD]
[TD="align: right"]2[/TD]
[TD]840-00361-01[/TD]
[/TR]
[TR]
[TD]840-00362-01[/TD]
[TD="align: right"]2[/TD]
[TD]840-00343-01 [/TD]
[/TR]
[TR]
[TD]840-00362-00[/TD]
[TD="align: right"]2[/TD]
[TD]840-00308-01 [/TD]
[/TR]
[TR]
[TD]840-00361-01[/TD]
[TD="align: right"]3[/TD]
[TD]840-00303-01 [/TD]
[/TR]
[TR]
[TD]840-00361-00[/TD]
[TD="align: right"]3[/TD]
[TD]840-00301-00[/TD]
[/TR]
[TR]
[TD]840-00343-01 [/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]840-00343-00 [/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]840-00308-01 [/TD]
[TD="align: right"]5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]840-00308-00 [/TD]
[TD="align: right"]5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]840-00303-01 [/TD]
[TD="align: right"]6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]840-00303-00 [/TD]
[TD="align: right"]6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]840-00301-00[/TD]
[TD="align: right"]7[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

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