Excel Loop Formula

scowan

New member
Joined
Nov 20, 2014
Messages
4
Reaction score
0
Points
0
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.
 
Hi scowan, I don't believe a lookup will work because it stops looking at the very 1st line it finds.
I'm not sure if this will help, but it did work in my test.
Problem might be, to make it work you will need to select all your cells and sort them by "Part" and then "by K or R".
Then in cell D2 enter this formula and copy down.
Code:
=IF(B2=B1,IF(D1="K","K","R"),C2)

So basically ALL lines will end up with a K next to them unless ALL stores with that product have and R.

I'll attach my sample.View attachment K or R.xlsx
 
One way is to use an array formula (press Cntl + shift + enter) after typing and drag down. No sort required

=IF(SUM(($B$2:$B$12=B2)*($C$2:$C$12="K"))>0,"K","R")
 
Tried but couldnt get it to work

One way is to use an array formula (press Cntl + shift + enter) after typing and drag down. No sort required

=IF(SUM(($B$2:$B$12=B2)*($C$2:$C$12="K"))>0,"K","R")

Hi WizardofOz,
I tried your array formula but could not get it to work properly. Maybe it is me :).
 
Back
Top