May 12 2019 06:34 AM - edited May 12 2019 06:37 AM
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.
May 12 2019 10:20 AM
@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).
May 12 2019 12:02 PM
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.
May 12 2019 09:14 PM
SolutionIn 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)
May 13 2019 05:23 AM
@Twifoo- I can't thank you enough - worked like a charm!!!
May 12 2019 09:14 PM
SolutionIn 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)