Apr 23 2020 02:10 AM
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
Apr 23 2020 04:08 AM
For such model
formula in F2 could be
=IF(($D2=$E2)*(COUNTIF($D:$D,$E2)=1),"Made", "No")
and drag it down
Apr 23 2020 04:18 AM
@Sergei Baklan You Sir, are a genius!!! thanks so much. This did the trick (not sure I understand it yet :) )