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)

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!!!

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 13, 2019
      The pleasure is mine.