I am trying to develop a formula that analyzes a specific material that has multiple inputs on a datapoint (MS). There needs to be only one identified and it can be deciphered based on the criteria of the other columns that exist in the excel sheet (“MRPcn and X-Plant”). I know that I need to utilize If/And/Or statements and I have done some advanced, but can’t understand how to do it due to the fact that these duplicate rows are not spread out by blank rows and there is multiple and differential logical tests that need to be ran. So, some cells will need to be absolute referenced in the formula and others will not.
An example of this is as follows: If a material has two material status both N2 and P2, indicating both production and engineering we look to the MRPcn and X-plant to identify what the correct single identification code needs to be under MS. I’ve conditionally formatted MRPcn so that it outputs colors respective to my legend. Ex: C43 = Production Planning = Marked with yellow whereas N0E = Engineering = Marked with blue. X-Plant consists of the same set of characters as MS, P2/N2/Etc. X-Plant and MRPcn are based on the client level within SAP so they are consistent across both rows of a material
By: Mic Fitzgerald