Need Formula data based on Multiple text/value conditions

[harinsh]

Hi Team,

Can anyone help me with below requirement....here is the data

Customer	Product Name	Amount	Contribution by	Final Status
Cust1	Product 1	17,991	Team 1
Cust2	Product 2	19,901	Team 2
Cust3	Product 3	18,948	Team 3
Cust3	Product 3	10,730	Team 1
Cust5	Product 5	16,754	Team 3
Cust6	Product 6	12,324	Team 1
Cust7	Product 7	11,813	Team 3
Cust8	Product 8	11,634	Team 1
Cust9	Product 9	17,742	Team 2
Cust9	Product 9	13,426	Team 3
Cust11	Product 11	14,678	Team 1
Cust12	Product 12	17,431	Team 3
Cust14	Product 14	14,932	Team 3
Cust14	Product 14	17,393	Team 3
Cust15	Product 14	18,962	Team 3
Cust16	Product 16	14,647	Team 1
Cust17	Product 17	16,890	Team 1
Cust18	Product 18	10,779	Team 1
Cust20	Product 19	11,129	Team 1
Cust20	Product 19	10,422	Team 2

<tbody>
</tbody>

Looking for formula to get the final status as which team owned the product/customer. Assume there are only three teams leading the projects and each team associates with customer. If customer and product same and amount greater in value then I should get the which team owned the project.....for example if customer 20 and product 19 contributed by two teams however I should get T1 Own because greater amount pertains to Team 1.

Here should be the output....

Customer	Product Name	Amount	Contribution by	Final Status
Cust1	Product 1	17,991	Team 1	T1 Own
Cust2	Product 2	19,901	Team 2	T2 Own
Cust3	Product 3	18,948	Team 3	T3 Own
Cust3	Product 3	10,730	Team 1	T3 Own
Cust5	Product 5	16,754	Team 3	T3 Own
Cust6	Product 6	12,324	Team 1	T1 Own
Cust7	Product 7	11,813	Team 3	T3 Own
Cust8	Product 8	11,634	Team 1	T1 Own
Cust9	Product 9	17,742	Team 2	T2 Own
Cust9	Product 9	13,426	Team 3	T2 Own
Cust11	Product 11	14,678	Team 1	T1 Own
Cust12	Product 12	17,431	Team 3	T3 Own
Cust14	Product 14	14,932	Team 3	T3 Own
Cust14	Product 14	17,393	Team 3	T3 Own
Cust15	Product 14	18,962	Team 3	T3 Own
Cust16	Product 16	14,647	Team 1	T1 Own
Cust17	Product 17	16,890	Team 1	T1 Own
Cust18	Product 18	10,779	Team 1	T1 Own
Cust20	Product 19	11,129	Team 1	T1 Own
Cust20	Product 19	10,422	Team 2	T1 Own

<tbody>
</tbody>

Let me know if any further clarification. Your help greatly appreciate ...Thank you

 

[Andrew Fergus]

Hello

The following array formula will do what you want.

I'm assuming the data is in the range A1:E21 with a header row 1.

=SUBSTITUTE(INDEX($D$2:$D$21,MAX(IF($B$2:$B$21=B2,IF($C$2:$C$21=MAXIFS($C$2:$C$21,$B$2:$B$21,B2),ROW($A$2:$A$21))))-ROW($A$1)), "eam ","") & " own"

Once you have entered this formula, but before you press Enter to confirm it, instead press Ctrl+Shift+Enter at the same time to make this into an array formula. You will know you have done this correctly when curly brackets {} automatically appear around the formula.

I trust this helps.
Andrew

 

[harinsh]

Hello

The following array formula will do what you want.

I'm assuming the data is in the range A1:E21 with a header row 1.

=SUBSTITUTE(INDEX($D$2:$D$21,MAX(IF($B$2:$B$21=B2,IF($C$2:$C$21=MAXIFS($C$2:$C$21,$B$2:$B$21,B2),ROW($A$2:$A$21))))-ROW($A$1)), "eam ","") & " own"

Once you have entered this formula, but before you press Enter to confirm it, instead press Ctrl+Shift+Enter at the same time to make this into an array formula. You will know you have done this correctly when curly brackets {} automatically appear around the formula.

I trust this helps.
Andrew

Thanks for your reply...I am not getting the output as I am getting "#NAME?"

 

[Andrew Fergus]

Does it error on every cell or just the duplicates? Does your Excel have the MAXIFS function? You can test it with a formula like this:
=MAXIFS($C$2:$C$21,$B$2:$B$21,B2)
Does that formula return a value or an error if you copy it down?

 

[Aladin Akyurek]

Does it error on every cell or just the duplicates? Does your Excel have the MAXIFS function? You can test it with a formula like this:
=MAXIFS($C$2:$C$21,$B$2:$B$21,B2)
Does that formula return a value or an error if you copy it down?

Andrew: OP does not have MAXIFS, hence the #NAME ? error. Looks like the MAXIFS bit can be replaced with MAX(IF($B$2:$B$21=B2,$C$2:$C$21)).

 

[harinsh]

Andrew: OP does not have MAXIFS, hence the #NAME ? error. Looks like the MAXIFS bit can be replaced with MAX(IF($B$2:$B$21=B2,$C$2:$C$21)).

Not understand where to replace and I could not find the MAXIFS function in my excel. Please share the updated formula now...

 

[Aladin Akyurek]

Not understand where to replace and I could not find the MAXIFS function in my excel. Please share the updated formula now...

In D2 ontrol+shift+enter, not just enter, and copy down:

=SUBSTITUTE(INDEX($D$2:$D$21,MAX(IF($B$2:$B$21=B2,IF($C$2:$C$21=MAX(IF($B$2:$B$21=B2,$C$2:$C$21)),ROW($A$2:$A$21))))-ROW($A$1)), "eam ","") & " own"

 

[harinsh]

Hi,

Yes, its working fine...could you quickly explain how does its working and if you give me little background of this formula so, that I can understand used functions....Thank you for your help...

 

[harinsh]

Hi,

Yes, its working fine...could you quickly explain how does its working and if you give me little background of this formula so, that I can understand used functions....Thank you for your help...

Could you please tweak the formula little bit...as even if product changes ( may not be same for all the cases) still it should work based on customer and amount basis...Thank you

 

[Andrew Fergus]

Hello harnish

It appears you want Cust 15 to be handled differently to Cust 13 and 14.....if Cust 15 had a different team (e.g. Team 1), I'm assuming the answer would be Team 1? This was not factored into the original formula sorry.

Try this array/CSE formula: (use control+shift+enter)

=INDEX($D$2:$D$21,MAX(IF($A$2:$A$21=A2,IF($B$2:$B$21=B2,IF($C$2:$C$21=MAX(IF($A$2:$A$21=A2,IF($B$2:$B$21=B2,$C$2:$C$21))),ROW($A$2:$A$21)))))-ROW($A$1))


this part of the old formula
=SUBSTITUTE( [insert formula above] , "eam ","") & " own"
converts the answer from "Team 1" to "T1 own". I have removed that from the latest formula to make it easier to follow, but can be re-introduced if you want it.

This is quite a complex formula and there is a lot going on, but I will attempt to explain it.

Normally we have formulas that say things like if A2=A3, but here we are doing things like if A2:A21=A2. What we are doing is comparing all of the values in A2:A21 and trying to find all instances in A2:A21 where there is the same value as that in A2 (same for B & C). Where you are comparing a value against multiple values (which is an array) then you need to use an array version of the formula, hence the need to use CSE to force the formula to be an array formula.

The formula uses an index function; an index function takes in an array of values (i.e. $D$2:$D$21), the 2nd part of the INDEX formula stipulates which row of that array to look at, and the third part of the INDEX formula stipulates which column of that array to look at, and the formula returns the value held in the array at the intersection of the stipulated row and column. Note in this instance we only have 1 column so that part has been omitted from this formula (the value of 1 is assumed by the formula where that value is not stipulated, and we only have one column being D).

Example: = INDEX(A1:D4,2,3) will return the value held in cell C2 being the 2nd row and 3rd column of the array. We used a slight variation where we say for instance =INDEX(D2:D21,3) which returns the value in cell D4 (note that D4 is the third row in the array, not the 3rd row in the sheet, more on this later) and we didn't stipulate the column given there is only 1.

Please note that not all formulas that use the INDEX function have to be CSE formulas.

How do we know which row to return?
This first part:
MAX(IF($A$2:$A$21=A2,IF($B$2:$B$21=B2,IF($C$2:$C$21=
is stating that we are wanting the maximum of something* where the customer has the same name (A), the product number is the same (B) and the value in C also matches something (expanded below); the value in C is an equally complicated calculation.

The C part looks like this:
IF($C$2:$C$21=MAX(IF($A$2:$A$21=A2,IF($B$2:$B$21=B2,$C$2:$C$21)))
We are trying to find where the value in C which is the maximum value in C but only where the values in columns A and B also match.
So every time there is the same cust and product, the formula returns all the values in C, figures out the maximum value and then compares that to the values in C2:C21.

Put into words, the formula would be like this:

=INDEX($D$2:$D$21,MAX( IF (same A & B, give me C where C = highest C where both A and B match), row number) - header row number)

*Max of what? What I have done is returned the row number of the highest matching value in C using ROW($A$2:$A$21), but where other parts of the array don't match the criteria, the formula is returning a bunch of zeroes. The max part finds the instance where all the data matches and gives us the row number (i.e. max (0,0,0,0,16754,0,0,0 etc for Cust 5) returns the value 16754 for Cust 5. So where the cust is the same and the product is the same, this finds the row where the value in C is the maximum for that criteria. Note however this is the row of the sheet and not the array, hence we deduct the row number of the header row being -ROW($A$1). This means you can move the table of data around the sheet and the formula should still work.

I trust this helps.
Andrew