1. ## Excel Loop Formula

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.  Reply With Quote

2. 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.K or R.xlsx  Reply With Quote

3. 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")  Reply With Quote

4. ## Tried but couldnt get it to work Originally Posted by WizzardOfOz 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 .  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•