Results 1 to 4 of 4

Thread: Excel Loop Formula

  1. #1

    Excel Loop Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #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

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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")

  4. #4

    Tried but couldnt get it to work

    Quote Originally Posted by WizzardOfOz View Post
    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 .

Posting Permissions

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