SOLVED

Compare columns on 2 sheets and add a condition > 0 value

%3CLINGO-SUB%20id%3D%22lingo-sub-1949998%22%20slang%3D%22en-US%22%3ECompare%20columns%20on%202%20sheets%20and%20add%20a%20condition%20%26gt%3B%200%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1949998%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20experts!%3C%2FP%3E%3CP%3EWill%20try%20to%20explain%20as%20clearly%20as%20I%20can%3A%20I%20need%20to%20understand%20if%20an%20incorrect%20rate%20(%26gt%3B0%25)%20was%20applied%20to%20orders%20based%20on%20specific%20material%20numbers%20(which%20should%20have%20a%200%25%20rate).%3C%2FP%3E%3COL%3E%3CLI%3Esheet%201%20is%20my%20master%20data%2C%20including%20order%20number%2C%20material%20number%20and%20%25%20rate%20(in%20number%20format%2C%20e.g.%2015%20for%2015%25)%3C%2FLI%3E%3CLI%3Esheet%202%20has%20a%20list%20of%20material%20numbers%20which%20may%20or%20may%20not%20appear%20in%20sheet%201%20(1%20column%20only).%20This%20list%20of%20material%20numbers%20should%20show%20up%20a%200%25%20rate%20in%20sheet%201.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EI%20believe%20the%20logic%20should%20do%20the%20following%3A%3C%2FP%3E%3CP%3EIf%20the%20material%20number%20in%20sheet%201%20is%20present%20in%20sheet%202%20(column%201)%20and%20the%20rate%20in%20sheet%201%20for%20that%20row%2Forder%20is%20%26gt%3B%200%2C%20the%20row%20in%20sheet%201%20should%20return%20a%20value%20%22incorrect%20rate%22%20in%20column%2047%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20already%20formatted%20the%20sheets%20to%20allow%20number%20values%20to%20be%20recognised%20and%20to%20have%20the%20first%20columns%20match%20on%20both%20sheets%20as%20the%20initial%20column.%3C%2FP%3E%3CP%3EIn%20case%20it%20is%20needed%2C%20I%20also%20applied%20the%20Name%20Manager%20to%20label%20certain%20columns%3A%3C%2FP%3E%3CP%3EMaterial_Order%20for%20sheet%201%20material%20numbers%20in%20column%201%3CBR%20%2F%3EMaterial_Ref%20for%20sheet%202%20material%20numbers%20in%20column%201%3CBR%20%2F%3EOrder%20for%20sheet%201%20in%20column%205%3CBR%20%2F%3ERate%20for%20sheet%201%20in%20column%2021%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20navigate%20the%20seas%20of%20formulas%20(IF%2C%20VLOOKUPS%2C%20AND%2C%20etc.)%20to%20get%20there%20as%20easily%20as%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGratefully%20yours%2C%3C%2FP%3E%3CP%3ERosalie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1949998%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-1952420%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20columns%20on%202%20sheets%20and%20add%20a%20condition%20%26gt%3B%200%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1952420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665034%22%20target%3D%22_blank%22%3E%40RosalieH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%26nbsp%3B%20already%20identified%20some%20of%20the%20most%20likely%20functions%20to%20be%20used%20to%20resolve%20your%20situation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBeyond%20that%2C%20I%20think%20the%20most%20helpful%20thing%20you%20can%20do%20to%20help%20us%20help%20you%20is%20post%20a%20copy%20of%20the%20actual%20spreadsheet%2C%20so%20long%20as%20no%20proprietary%20or%20confidential%20information%20is%20in%20it.%20If%20that's%20the%20case%2C%20post%20something%20that%20resembles%20it%2C%20but%20has%20nonsense%20names%20and%20labels.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOtherwise%20we're%20forced%20to%20try%20to%20re-create%20the%20layout%20from%20your%20verbal%20description%2C%20and%20that%20is%20far%20less%20productive%20than%20working%20with%20the%20actual%20layout.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1952475%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20columns%20on%202%20sheets%20and%20add%20a%20condition%20%26gt%3B%200%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1952475%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665034%22%20target%3D%22_blank%22%3E%40RosalieH%3C%2FA%3E%26nbsp%3BThe%20attached%20example%20may%20help%20you%20find%20a%20solution%20to%20your%20question.%20If%20not%2C%20you%20can%20perhaps%20upload%20the%20file%20you%20are%20working%20on%2C%20after%20having%20removed%20any%20private%20and%20confidential%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-12-03%20at%2015.17.36.png%22%20style%3D%22width%3A%20598px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237525i0B6EC942FC7EC5B6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-12-03%20at%2015.17.36.png%22%20alt%3D%22Screenshot%202020-12-03%20at%2015.17.36.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi experts!

Will try to explain as clearly as I can: I need to understand if an incorrect rate (>0%) was applied to orders based on specific material numbers (which should have a 0% rate).

  1. sheet 1 is my master data, including order number, material number and % rate (in number format, e.g. 15 for 15%)
  2. sheet 2 has a list of material numbers which may or may not appear in sheet 1 (1 column only). This list of material numbers should show up a 0% rate in sheet 1.

I believe the logic should do the following:

If the material number in sheet 1 is present in sheet 2 (column 1) and the rate in sheet 1 for that row/order is > 0, the row in sheet 1 should return a value "incorrect rate" in column 47

 

I already formatted the sheets to allow number values to be recognised and to have the first columns match on both sheets as the initial column.

In case it is needed, I also applied the Name Manager to label certain columns:

Material_Order for sheet 1 material numbers in column 1
Material_Ref for sheet 2 material numbers in column 1
Order for sheet 1 in column 5
Rate for sheet 1 in column 21

 

Can anyone help me navigate the seas of formulas (IF, VLOOKUPS, AND, etc.) to get there as easily as possible?

 

Many thanks in advance!

 

Gratefully yours,

Rosalie

3 Replies

@RosalieH 

 

You've  already identified some of the most likely functions to be used to resolve your situation.

 

Beyond that, I think the most helpful thing you can do to help us help you is post a copy of the actual spreadsheet, so long as no proprietary or confidential information is in it. If that's the case, post something that resembles it, but has nonsense names and labels.

 

Otherwise we're forced to try to re-create the layout from your verbal description, and that is far less productive than working with the actual layout.

best response confirmed by RosalieH (New Contributor)
Solution

@RosalieH The attached example may help you find a solution to your question. If not, you can perhaps upload the file you are working on, after having removed any private and confidential information.

 Screenshot 2020-12-03 at 15.17.36.png

 

Thank you so much for the help, Riny! This worked a treat! I wish you all the best for the holiday season; stay safe!