I am trying to develop a formula that analyzes a specific material (corresponding to a Part Number
value) that has multiple values of MS
tied to a hierarchy. This happens in many related ERP-using companies due to incorrect documentation and proper maintenance in the material master. The correct value can be determined solely from the corresponding MRPcn
and X-Plant
values, which are always consistent as they are based on the Client level within SAP.
I've conditionally formatted the MRPcn
column so the values are categorized by color according to the legend. Conditional formatting was done because there are 500+ unique MRPcn
values. This way, I could filter or write a formula using the colors if needed.
There are 20 unique X-Plant
values. 7 of those are used for the MS
values. These are P2
(Production), N2
(Engineering), 18
/19
/4
/1
(Obsolete), and 15
(Global Ops/Service). These have also been conditionally formatted. Note that some of the X-Plant
values don't fall under just these categories but these four are the most relevant and consistent across the dataset.
I have been trying to utilize IF
/AND
/OR
statements and have previously done some advanced work with these, but the formula will end up large and unwieldy due to the large number of different unique values that need to be incorporated. Plus I'm also having trouble even creating a partial formula using just a small number of values.
Since the correct value of MS
actually depends on the category of the MRPcn
value and the category of the X-Plant
value, I was hoping to somehow leverage the background color of the cells (which correspond to the category) to simplify the formula.
As an example, if the MS
values of a part are P2
and N2
, and MRPcn
= C43
(Production Planning) and X-Plant
= P2
, we know the correct MS
is P2
. (The reason a material might have MS
values of P2
and N2
, which are polar opposites, is because the part may have been initiated in SAP as an engineering planning part during NPI but then later down the road moved to production in a manufacturing plant and not maintained in the MM.)
Here is more sample data showing some corrections to MS
:
Looking at rows 85:86
, part 1301386
has two different MS
values of 18
and P2
which denote Discontinued/Obsolete and Production, an MRPcn
that is Engineering, and an X-Plant
that is Discontinued, so it's likely that that part needs its MS
to be 18
(Obsolete).
Acronym definitions:
- MM = Material Master
- MRPcn = Materials Resource Planning controller
- MS = Material Status
- NPI = New Product Introduction.
- X-Plant MS - Client level material status identifying a blockage or status of a material in regards to supply chain planning