Excel Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1331569%22%20slang%3D%22en-US%22%3EExcel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331569%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20stumped%20on%20something%20and%20suspect%20that%20the%20answer%20lies%20in%20an%20array%20formula.%26nbsp%3B%20Hard%20to%20explain%20but%20here%20goes....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20sheet%20has%20multiple%20rows.%26nbsp%3B%20In%20column%20L%20i%20have%20a%20unique%20Identifier%20that%20links%20the%20rows%26nbsp%3B%20-for%20example%2C%20rows%202-5%20could%20be%20using%20ID%20ABC1%2C%20rows%206%26amp%3B7%20XYZ2%2C%20Rows%208-16%20use%20SSS5%20etc.%26nbsp%3B%20There%20is%20no%20pattern%20to%20the%20number%20of%20rows%20using%20an%20indentifer.%20Each%20row%20has%20its%20own%20values%20for%20that%20row%20but%20their%20common%20linkage%20is%20column%20L%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20column%20T%20I%20have%20a%20value%20for%20that%20row.%26nbsp%3B%20So%2C%20for%20identifer%20ABC1%2C%20the%20values%20could%20be%3A%20-%3C%2FP%3E%3CP%3ERow%202%20%3D%20100%3C%2FP%3E%3CP%3ERow%203%20%3D%20100%3C%2FP%3E%3CP%3ERow%204%20%3D%20150%3C%2FP%3E%3CP%3ERow%205%20%3D%2097%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20column%20U%2C%20I%20have%20a%20value%20via%20a%20Vlookup%20from%20a%20Pivot%20table%20which%20pulls%20in%20the%20MAX%20value%20of%20each%20unique%20Identifier.%20So%2C%20in%20the%20above%20example%2C%20the%20value%20of%20150%20(which%20is%20the%20MAX%20value%20of%20Identifier%20ABC1)%20apears%20in%20columnn%20U%20for%20all%204%20rows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ERow%20%23%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BCol%20L%20(ID)%26nbsp%3B%20%26nbsp%3B%20Col%20T%20(Uniqe%20Val)%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BCol%20U%20(MAX)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BABC1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B100%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20150%3C%2FP%3E%3CP%3E3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BABC1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B100%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20150%26nbsp%3B%3C%2FP%3E%3CP%3E4%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BABC1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B150%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20150%3C%2FP%3E%3CP%3E5%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BABC1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B97%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20150%3C%2FP%3E%3CP%3E6%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BXYZ2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B350%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B350%3C%2FP%3E%3CP%3E7%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BXYZ2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B350%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B350%3C%2FP%3E%3CP%3EHere%20is%20my%20challenge.%26nbsp%3B%20In%20my%20next%20column%20(Col%20V)%20I%20need%20a%20formula%20that%20will%20do%20the%20following.%3C%2FP%3E%3CP%3EFor%20all%20rows%20where%20the%20Column%20L%20Unique%20ID%20is%20the%20same%20value%2C%20perform%20the%20following%20calculation%3A%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%3A%20If%20the%20value%20in%20column%20U%20is%20the%20same%20as%20its%20value%20in%20column%20T%20AND%20its%20the%20only%20row%20within%20that%20unique%20identifer%20where%20the%20values%20are%20the%20same%20(just%20like%20my%20example%20-%20Column%20U%20value%20of%20150%20matches%20column%20T%20value%20on%20only%201%20row)%26nbsp%3Breturn%20result%20of%20%22Decision%20Made%22%3C%2FP%3E%3CP%3EStep%202%20%3A%20If%20the%20above%20condition%20is%20not%20met%2C%20return%20result%20of%20%22No%20Decision%22.%26nbsp%3B%20Note%20the%20parameters%20that%20could%20drive%20no%20decision%20are%20a)%20where%20all%20values%20in%20column%20U%20are%20the%20same%20for%20that%20identifier%20or%20b)%20more%20than%20one%20row%20per%20identifier%20are%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20needs%20to%20'reset'%20every%20time%20is%20sees%20a%20new%20identifier%20and%20perform%20the%20calc%20again%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1331569%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-1331827%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F633927%22%20target%3D%22_blank%22%3E%40Gaffers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20328px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F186098i6FF2D14E9A0823D0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20in%20F2%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF((%24D2%3D%24E2)*(COUNTIF(%24D%3A%24D%2C%24E2)%3D1)%2C%22Made%22%2C%20%22No%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, I'm stumped on something and suspect that the answer lies in an array formula.  Hard to explain but here goes....

 

My sheet has multiple rows.  In column L i have a unique Identifier that links the rows  -for example, rows 2-5 could be using ID ABC1, rows 6&7 XYZ2, Rows 8-16 use SSS5 etc.  There is no pattern to the number of rows using an indentifer. Each row has its own values for that row but their common linkage is column L

 

In column T I have a value for that row.  So, for identifer ABC1, the values could be: -

Row 2 = 100

Row 3 = 100

Row 4 = 150

Row 5 = 97

 

 

In column U, I have a value via a Vlookup from a Pivot table which pulls in the MAX value of each unique Identifier. So, in the above example, the value of 150 (which is the MAX value of Identifier ABC1) apears in columnn U for all 4 rows

 

Row #     Col L (ID)    Col T (Uniqe Val)       Col U (MAX)

2             ABC1             100                              150

3             ABC1             100                              150 

4             ABC1             150                              150

5             ABC1               97                              150

6             XYZ2             350                               350

7             XYZ2             350                               350

Here is my challenge.  In my next column (Col V) I need a formula that will do the following.

For all rows where the Column L Unique ID is the same value, perform the following calculation: -

 

Step 1: If the value in column U is the same as its value in column T AND its the only row within that unique identifer where the values are the same (just like my example - Column U value of 150 matches column T value on only 1 row) return result of "Decision Made"

Step 2 : If the above condition is not met, return result of "No Decision".  Note the parameters that could drive no decision are a) where all values in column U are the same for that identifier or b) more than one row per identifier are the same.

 

The formula needs to 'reset' every time is sees a new identifier and perform the calc again

 

2 Replies
Highlighted

@Gaffers 

For such model

image.png

formula in F2 could be

=IF(($D2=$E2)*(COUNTIF($D:$D,$E2)=1),"Made", "No")

and drag it down

Highlighted

@Sergei Baklan You Sir, are a genius!!! thanks so much.  This did the trick (not sure I understand it yet )