Forum Discussion

ehb029's avatar
ehb029
Copper Contributor
May 12, 2019
Solved

Need FORMULA help please!!

I have been trying to create a formula for a few days with no luck.  The logic I'm trying to design:   1) customer # in row 1 is different than row 2 - always move value in (i1) to a new column (j...
excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    May 12, 2019

    ehb029 

    In the attached version of your file, the formula in J2, copied down rows, is: 

    =IF((B2=B1)*(D2<>"(All)")*SUMPRODUCT((B$1:B1=B2)*(D$1:D1="(All)")),
    "",
    I2)

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 12, 2019

ehb029 , the logic defined is different from what is in your sample file.

 

If Customer # in next row is not equal to Customer # in current row then J1=I1

Here is different

 

#3 is unclear If All doesn't exists that means something else is at this place, thus value <> All, thus J1 shall be blank (see item 2).

  • ehb029's avatar
    ehb029
    Copper Contributor
    May 12, 2019

    SergeiBaklan 

     

    Thanks for your feedback. 

    When the cust # changes i always need the numeric value in col i moved to col j. 

    However,

    When the cust # changes or remains the same and the "all" is present, i need the value moved - but when the customer # is the same and the "all" is replaced with another value, I want to leave the column blank.... this piece is where I am having problems. 

    I added v1 of the spreadsheet so you can see the formula I've tried and I put yellow hightlight where it's not working. 

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 12, 2019

      ehb029 

      In the attached version of your file, the formula in J2, copied down rows, is: 

      =IF((B2=B1)*(D2<>"(All)")*SUMPRODUCT((B$1:B1=B2)*(D$1:D1="(All)")),
      "",
      I2)

      • ehb029's avatar
        ehb029
        Copper Contributor
        May 13, 2019

        Twifoo- I can't thank you enough - worked like a charm!!!