SOLVED

Need FORMULA help please!!

Copper Contributor

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 (j1). 

2) if customer # is the same - check another value in a col d and if equal to the word "all", move value in (i1) to new column (j1) but if <> "all", move a blank to column (j1).  

3) if customer # is the same - and "all" does not exist, move value in (i1) to (j1)

see attached file for sample output and thank you in advance. 

5 Replies

@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

image.png

 

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

@Sergei Baklan 

 

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. 

best response confirmed by ehb029 (Copper Contributor)
Solution

@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)

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

The pleasure is mine.
1 best response

Accepted Solutions
best response confirmed by ehb029 (Copper Contributor)
Solution

@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)

View solution in original post