Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1499186%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1499186%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20that%20will%20search%20text%20and%20return%20only%20the%20value%20that%20has%20the%20greatest%20number%20at%20the%20end%2C%20but%20the%20rest%20of%20the%20value%20prior%20to%20the%20last%20number%20must%20match.%20I%20have%20attached%20a%20photo%20below%20and%20the%20file%20to%20this%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20search%20column%20K%20and%20have%20a%20formula%20in%20column%20L%20that%20returns%20the%20same%20value%20if%20the%20last%20number%20in%20the%20column%20K%20value%20is%20the%20greatest%20out%20of%20any%20value%20in%20column%20K%20when%20the%20rest%20of%20the%20value%20matches.%20I%20have%20typed%20out%20the%20result%20I%20would%20like%20for%20the%20first%203%20rows.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22excel%20help.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202208i4C79E9307B1C98AC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22excel%20help.PNG%22%20alt%3D%22excel%20help.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1499186%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1499804%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1499804%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F714531%22%20target%3D%22_blank%22%3E%40Benvett14%3C%2FA%3E%26nbsp%3B%20I%20have%20created%20a%20formula%20that%20works%20but%20it%20is%20restrictive%20with%20some%20assumptions%20and%20need%20input%20from%20you%20on%20specific%20formats%20that%20might%20be%20in%20col%20K.%26nbsp%3B%20Here%20is%20the%20formula%20I%20put%20into%20L661%20and%20entered%20as%20an%20array%20formula%20(ctrl%2Bshift%2Benter)%20and%20filled%20down%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(MAX(--(LEFT(%24K%24658%3A%24K%24669%2C11)%3DLEFT(K661%2C11))*RIGHT(%24K%24658%3A%24K%24669%2C1))%3D--RIGHT(K661%2C1)%2CK661%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20that%20assumes%20the%20first%2011%20characters%20identify%20the%20unique%20first%20part%20and%20the%20last%20%22rev%22%20digit%20is%20only%201%20character.%26nbsp%3B%20I%20know%20neither%20of%20these%20may%20be%20true%20in%20all%20cases%20but%20didn't%20want%20to%20mess%20with%20more%20elaborate%20formulas%20until%20you%20explicitly%20and%20fully%20defined%20the%20formatting%20for%20col%20K.%26nbsp%3B%20for%20example%20will%20the%20first%20triple%20pair%20of%20digits%20before%20the%20first%20%22.%22%20always%20be%20a%20triple%20pair%20of%20digits%20with%20a%20single%20space%20between%20each%3F%26nbsp%3B%20can%20that%20second%20number%20that%20is%20between%20the%202%20%22.%22s%20always%20only%201%20or%202%20digits%20(e.g.%200-99)%3F%26nbsp%3B%20If%20those%20are%20both%20true%20than%20the%20'simple'%20left(...%20%2C%2011)%20will%20work.%26nbsp%3B%20And%20finally%20is%20that%20last%20number%20always%20a%20single%20digit%20(e.g.%200-9)%20or%20could%20it%20be%202%20or%203%20digits%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20and%20WAIT%2C%20is%20there%20any%20reason%20the%20formula%20can%20point%20back%20to%20columns%20B%2C%20C%20and%20D%20from%20which%20you%20are%20'CONCATENATE'%20them%20to%20create%20col%20K%3F%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to create a formula that will search text and return only the value that has the greatest number at the end, but the rest of the value prior to the last number must match. I have attached a photo below and the file to this post.

 

I am trying to search column K and have a formula in column L that returns the same value if the last number in the column K value is the greatest out of any value in column K when the rest of the value matches. I have typed out the result I would like for the first 3 rows.

excel help.PNG

 

1 Reply

@Benvett14  I have created a formula that works but it is restrictive with some assumptions and need input from you on specific formats that might be in col K.  Here is the formula I put into L661 and entered as an array formula (ctrl+shift+enter) and filled down:

 

=IF(MAX(--(LEFT($K$658:$K$669,11)=LEFT(K661,11))*RIGHT($K$658:$K$669,1))=--RIGHT(K661,1),K661,"")

 

but that assumes the first 11 characters identify the unique first part and the last "rev" digit is only 1 character.  I know neither of these may be true in all cases but didn't want to mess with more elaborate formulas until you explicitly and fully defined the formatting for col K.  for example will the first triple pair of digits before the first "." always be a triple pair of digits with a single space between each?  can that second number that is between the 2 "."s always only 1 or 2 digits (e.g. 0-99)?  If those are both true than the 'simple' left(... , 11) will work.  And finally is that last number always a single digit (e.g. 0-9) or could it be 2 or 3 digits?  

 

EDIT: and WAIT, is there any reason the formula can point back to columns B, C and D from which you are 'CONCATENATE' them to create col K?