Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1570448%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570448%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20could%20you%20please%20help%20me%20with%20formula%20for%20below%20data-%3C%2FP%3E%3CTABLE%20width%3D%22235%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2257px%22%3EDoc%20Number%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3ELine%20Item%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2257px%22%3EDoc%20Max%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER12345%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER12345%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3EER12345%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER12345%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER12345%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER12345%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER12345%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER23456%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER23456%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3EER23456%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER23456%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER34567%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER34567%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER34567%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3EER34567%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2283px%22%20height%3D%2230px%22%3EER34567%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Edocument%20number%20should%20be%20updated%20in%20front%20of%20Max%20number%20line%20item%20(doc%20max%20row%20column)%20as%20shown%20in%20the%20above%20data%20could%20you%20please%20help%20me%20with%20the%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1570448%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eanalytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1574500%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574500%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751322%22%20target%3D%22_blank%22%3E%40vijaymestha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20find%20my%20solution%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1574563%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751322%22%20target%3D%22_blank%22%3E%40vijaymestha%3C%2FA%3E%26nbsp%3B%2C%20here%20are%20a%20couple%20of%20variations%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40ramizassaf%3C%2FA%3E%26nbsp%3B's%20solution%20if%20you%20don't%20have%20the%20Dynamic%20Array%20version%20of%20Excel.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi could you please help me with formula for below data-

Doc NumberLine ItemDoc Max
ER123455 
ER123456ER12345
ER123453 
ER123454 
ER123451 
ER123452 
ER234561 
ER234563ER23456
ER234562 
ER345672 
ER345673 
ER345674ER34567
ER345671 

document number should be updated in front of Max number line item (doc max row column) as shown in the above data could you please help me with the formula 

 

2 Replies

@vijaymestha 

 

please find my solution

@vijaymestha , here are a couple of variations to @Ramiz_Assaf 's solution if you don't have the Dynamic Array version of Excel.