• 393K Members
• 3,425 Online
• 424K Conversations
SOLVED

New 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

# Re: Need FORMULA help please!!

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

# Re: Need FORMULA help please!!

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.

Solution

# Re: Need FORMULA help please!!

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)

# Re: Need FORMULA help please!!

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

# Re: Need FORMULA help please!!

The pleasure is mine.
Related Conversations