I am trying to do a lookup on following sheet (4 columns Store, Part, K or R, New K or R):
Store Part K or R New K or R
1 ABC R
2 ABC K
3 ABC R
1 KLM K
2 KLM K
3 KLM R
1 QRS R
2 QRS R
3 QRS K
2 XYZ R
3 XYZ K
I would like to write a formula that will look at all rows that contain the same part number and look for a "K" in the K or R field. If it finds a K in any of the 3 stores, I want it to put a K in the New K or R cell for each store. If it does not find a K, I want it put a R in the cell for each store.
My reasoning logic: I am doing an obsolescence study and I want to analyze each part in all stores where the part exists. If any of the stores has a K (Keep), I want it to put a K for all stores. If it does not, it should put an R (Return). The point is that if we sell it in any store then we want to keep the inventory in all stores.
Store Part K or R New K or R
1 ABC R
2 ABC K
3 ABC R
1 KLM K
2 KLM K
3 KLM R
1 QRS R
2 QRS R
3 QRS K
2 XYZ R
3 XYZ K
I would like to write a formula that will look at all rows that contain the same part number and look for a "K" in the K or R field. If it finds a K in any of the 3 stores, I want it to put a K in the New K or R cell for each store. If it does not find a K, I want it put a R in the cell for each store.
My reasoning logic: I am doing an obsolescence study and I want to analyze each part in all stores where the part exists. If any of the stores has a K (Keep), I want it to put a K for all stores. If it does not, it should put an R (Return). The point is that if we sell it in any store then we want to keep the inventory in all stores.