SOLVED
Home

Need FORMULA help please!!

%3CLINGO-SUB%20id%3D%22lingo-sub-561501%22%20slang%3D%22en-US%22%3ENeed%20FORMULA%20help%20please!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561501%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20trying%20to%20create%20a%20formula%20for%20a%20few%20days%20with%20no%20luck.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20logic%20I'm%20trying%20to%20design%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20customer%20%23%20in%20row%201%20is%20different%20than%20row%202%20-%20always%20move%20value%20in%20(i1)%20to%20a%20new%20column%20(j1).%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20if%20customer%20%23%20is%20the%20same%20-%20check%20another%20value%20in%20a%20col%20d%20and%20if%20equal%20to%20the%20word%20%22all%22%2C%20move%20value%20in%20(i1)%20to%20new%20column%20(j1)%20but%20if%20%26lt%3B%26gt%3B%20%22all%22%2C%20move%20a%20blank%20to%20column%20(j1).%20%26nbsp%3B%3C%2FP%3E%3CP%3E3)%20if%20customer%20%23%20is%20the%20same%20-%20and%20%22all%22%20does%20not%20exist%2C%20move%20value%20in%20(i1)%20to%20(j1)%3C%2FP%3E%3CP%3Esee%20attached%20file%20for%20sample%20output%20and%20thank%20you%20in%20advance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-561501%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561652%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20FORMULA%20help%20please!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340877%22%20target%3D%22_blank%22%3E%40ehb029%3C%2FA%3E%26nbsp%3B%2C%20the%20logic%20defined%20is%20different%20from%20what%20is%20in%20your%20sample%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EIf%20Customer%20%23%20in%20next%20row%20is%20not%20equal%20to%20Customer%20%23%20in%20current%20row%20then%20J1%3DI1%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3EHere%20is%20different%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20711px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113089iE36D108EC6CA3DAA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%233%20is%20unclear%20If%20All%20doesn't%20exists%20that%20means%20something%20else%20is%20at%20this%20place%2C%20thus%20value%20%26lt%3B%26gt%3B%20All%2C%20thus%20J1%20shall%20be%20blank%20(see%20item%202).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561720%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20FORMULA%20help%20please!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20feedback.%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20cust%20%23%20changes%20i%20always%20need%20the%20numeric%20value%20in%20col%20i%20moved%20to%20col%20j.%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%3C%2FP%3E%3CP%3EWhen%20the%20cust%20%23%20changes%20%3CSTRONG%3Eor%3C%2FSTRONG%3E%20remains%20the%20same%20and%20the%20%22all%22%20is%20present%2C%20i%20need%20the%20value%20moved%20-%20but%20when%20the%20customer%20%23%20is%20the%20same%20and%20the%20%22all%22%20is%20replaced%20with%20another%20value%2C%20I%20want%20to%20leave%20the%20column%20blank....%20this%20piece%20is%20where%20I%20am%20having%20problems.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20added%20v1%20of%20the%20spreadsheet%20so%20you%20can%20see%20the%20formula%20I've%20tried%20and%20I%20put%20yellow%20hightlight%20where%20it's%20not%20working.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562104%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20FORMULA%20help%20please!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340877%22%20target%3D%22_blank%22%3E%40ehb029%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20version%20of%20your%20file%2C%20the%20formula%20in%20J2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF((B2%3DB1)*(D2%26lt%3B%26gt%3B%22(All)%22)*SUMPRODUCT((B%241%3AB1%3DB2)*(D%241%3AD1%3D%22(All)%22))%2C%3CBR%20%2F%3E%22%22%2C%3CBR%20%2F%3EI2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563234%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20FORMULA%20help%20please!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E-%20I%20can't%20thank%20you%20enough%20-%20worked%20like%20a%20charm!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563263%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20FORMULA%20help%20please!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563263%22%20slang%3D%22en-US%22%3EThe%20pleasure%20is%20mine.%3C%2FLINGO-BODY%3E
ehb029
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

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

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.
Related Conversations