Home

Replace item AUTO if not match in invoice No

%3CLINGO-SUB%20id%3D%22lingo-sub-533221%22%20slang%3D%22en-US%22%3EReplace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533221%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3Ei%20am%20working%20on%20a%20sheet%20on%20two%20set%20of%20data%20bases.%20first%20is%20main%20data%20and%20second%20is%20invoice%20data.%20both%20are%20extract%20from%20different%20system%20software%20for%20analysis.%20main%20data%20is%20perfect%20and%20compare%20into%20invoice%20data%20and%20check%20all%20items%20are%20OK%20or%20not%20OK.%20similar%20i%20have%20a%20long%20invoices%20data%20where%20i%20have%20to%20compare%20with%20main%20data.%20first%20check%20invoice%20then%20match%20all%20item%20if%20there%20is%20presidency%20on%20there%20then%20replace%20to%20each%20other.%20you%20can%20see%20my%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20am%20using%20formula%20but%20failed.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(MATCH(A3%2C%24F%243%3A%24F%2421%2C0)%26gt%3B0%2CA3%2CREPLACE(A3%2C1%2C11%2CF3))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-533221%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-533711%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533711%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333458%22%20target%3D%22_blank%22%3E%40majidsiddique52075%3C%2FA%3E%26nbsp%3B%2C%20you%20can't%20replace%20the%20value%20in%20another%20cell%20without%20VBA%20programming%2C%20formula%20returns%20the%20result%20of%20the%20calculations%20into%20the%20cell%20where%20this%20formula%20is.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20for%20formula%2C%20MATCH%20returns%20%23N%2FA%20error%20if%20match%20is%20not%20found%2C%20with%20that%20error%20entire%20formula%20also%20returns%20the%20same%20error.%20You%20may%20wrap%20the%20MATCH%20with%20IFNA%20or%20IFERROR%20to%20handle%20such%20situation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EREPLACE(A19%2C1%2C11%2CF19)%20takes%20text%20from%20A19%2C%20replaces%20it's%20first%20eleven%20characters%20with%20the%20text%20from%20F19%20and%20returns%20result%20into%20the%20cell%20where%20this%20formula%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-533957%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533957%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20to%20reply.%3C%2FP%3E%3CP%3Eis%20there%20any%20possibility%20to%20highlight%20mismatch%20item%20with%20conditional%20formatting%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534021%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534021%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333458%22%20target%3D%22_blank%22%3E%40majidsiddique52075%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20apply%20the%20rule%20with%20formula%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIF(%24F%243%3A%24F%2421%2CA3)%3D0%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534273%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534273%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20boss%2C%20its%20working%20perfect%2C%20but%20i%20cannot%20apply%20vlookup%2C%20match%20or%20any%20other%20formula%20to%20count%20highlight%20cell.%20its%20also%20required%20for%20VBA%20coding.%20i%20really%20dont%20know%20VBA.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply%2C%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534308%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333458%22%20target%3D%22_blank%22%3E%40majidsiddique52075%3C%2FA%3E%26nbsp%3B%2C%20to%20count%20them%20you%20may%20use%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(--(COUNTIF(%24F%243%3A%24F%2421%2C%24A%243%3A%24A%2421)%3D0))%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(--NOT(ISNUMBER(MATCH(%24A%243%3A%24A%2421%2C%24F%243%3A%24F%2421%2C0))))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534334%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534334%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eits%20working%20perfect.%20thank%20boss%2C%3C%2FP%3E%3CP%3Eif%20i%20insert%20manually%20color%20on%20the%20cell%20then%20formula%20is%20not%20working.%20is%20it%20for%20conditional%20formatting%20only%3F%3C%2FP%3E%3CP%3Edo%20you%20have%20any%20other%20idea%20to%20count%20cell%20highlighted%20when%20i%20put%20manually%20color%20by%20theme%20color%20window%2C%20(%20normal%20cell%20color%20)%3F%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534402%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333458%22%20target%3D%22_blank%22%3E%40majidsiddique52075%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20it%20counts%20using%20practically%20the%20same%20formulas%20as%20for%20conditional%20formatting%20rule.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20count%20based%20on%20cell%20property%2C%20color%20in%20particular%2C%20that's%20VBA%20programming.%20Here%20are%20at%20least%20couple%20of%20conversations%20how%20to%20do%20that.%20That's%20not%20my%20expertise.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534447%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20Boss%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534501%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20item%20AUTO%20if%20not%20match%20in%20invoice%20No%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534501%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333458%22%20target%3D%22_blank%22%3E%40majidsiddique52075%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
majidsiddique52075
Occasional Contributor

Hi All,

i am working on a sheet on two set of data bases. first is main data and second is invoice data. both are extract from different system software for analysis. main data is perfect and compare into invoice data and check all items are OK or not OK. similar i have a long invoices data where i have to compare with main data. first check invoice then match all item if there is presidency on there then replace to each other. you can see my attached file. 

i am using formula but failed. 

=IF(MATCH(A3,$F$3:$F$21,0)>0,A3,REPLACE(A3,1,11,F3))

 

Thanks.

9 Replies

@majidsiddique52075 , you can't replace the value in another cell without VBA programming, formula returns the result of the calculations into the cell where this formula is.

 

As for formula, MATCH returns #N/A error if match is not found, with that error entire formula also returns the same error. You may wrap the MATCH with IFNA or IFERROR to handle such situation.

 

REPLACE(A19,1,11,F19) takes text from A19, replaces it's first eleven characters with the text from F19 and returns result into the cell where this formula is.

Hi @Sergei Baklan 

Thank to reply.

is there any possibility to highlight mismatch item with conditional formatting? 

Thanks.

 

@majidsiddique52075 , you may apply the rule with formula

=COUNTIF($F$3:$F$21,A3)=0

Please see attached

 

@Sergei Baklan 

Thank boss, its working perfect, but i cannot apply vlookup, match or any other formula to count highlight cell. its also required for VBA coding. i really dont know VBA. 

Thanks for the reply,

Thanks

 

@majidsiddique52075 , to count them you may use

=SUMPRODUCT(--(COUNTIF($F$3:$F$21,$A$3:$A$21)=0))

or

=SUMPRODUCT(--NOT(ISNUMBER(MATCH($A$3:$A$21,$F$3:$F$21,0))))

 

@Sergei Baklan 

its working perfect. thank boss,

if i insert manually color on the cell then formula is not working. is it for conditional formatting only?

do you have any other idea to count cell highlighted when i put manually color by theme color window, ( normal cell color )?

Thanks 

 

@majidsiddique52075 , yes, it counts using practically the same formulas as for conditional formatting rule.

 

If count based on cell property, color in particular, that's VBA programming. Here are at least couple of conversations how to do that. That's not my expertise.

@Sergei Baklan 

Thanks a lot Boss,

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies