Hi Team,
Can anyone help me with below requirement....here is the data
[TABLE="class: outer_border, width: 323, align: left"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product Name[/TD]
[TD]Amount[/TD]
[TD]Contribution by[/TD]
[TD]Final Status[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD]Product 1[/TD]
[TD] 17,991[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust2[/TD]
[TD]Product 2[/TD]
[TD] 19,901[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]Product 3[/TD]
[TD] 18,948[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]Product 3[/TD]
[TD] 10,730[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust5[/TD]
[TD]Product 5[/TD]
[TD] 16,754[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust6[/TD]
[TD]Product 6[/TD]
[TD] 12,324[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust7[/TD]
[TD]Product 7[/TD]
[TD] 11,813[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust8[/TD]
[TD]Product 8[/TD]
[TD] 11,634[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust9[/TD]
[TD]Product 9[/TD]
[TD] 17,742[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust9[/TD]
[TD]Product 9[/TD]
[TD] 13,426[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust11[/TD]
[TD]Product 11[/TD]
[TD] 14,678[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust12[/TD]
[TD]Product 12[/TD]
[TD] 17,431[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust14[/TD]
[TD]Product 14[/TD]
[TD] 14,932[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust14[/TD]
[TD]Product 14[/TD]
[TD] 17,393[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust15[/TD]
[TD]Product 14[/TD]
[TD] 18,962[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust16[/TD]
[TD]Product 16[/TD]
[TD] 14,647[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust17[/TD]
[TD]Product 17[/TD]
[TD] 16,890[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust18[/TD]
[TD]Product 18[/TD]
[TD] 10,779[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust20[/TD]
[TD]Product 19[/TD]
[TD] 11,129[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust20[/TD]
[TD]Product 19[/TD]
[TD] 10,422[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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....
[TABLE="class: outer_border, width: 323"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product Name[/TD]
[TD]Amount[/TD]
[TD]Contribution by[/TD]
[TD]Final Status[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD]Product 1[/TD]
[TD] 17,991[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust2[/TD]
[TD]Product 2[/TD]
[TD] 19,901[/TD]
[TD]Team 2[/TD]
[TD]T2 Own[/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]Product 3[/TD]
[TD] 18,948[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]Product 3[/TD]
[TD] 10,730[/TD]
[TD]Team 1[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust5[/TD]
[TD]Product 5[/TD]
[TD] 16,754[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust6[/TD]
[TD]Product 6[/TD]
[TD] 12,324[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust7[/TD]
[TD]Product 7[/TD]
[TD] 11,813[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust8[/TD]
[TD]Product 8[/TD]
[TD] 11,634[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust9[/TD]
[TD]Product 9[/TD]
[TD] 17,742[/TD]
[TD]Team 2[/TD]
[TD]T2 Own[/TD]
[/TR]
[TR]
[TD]Cust9[/TD]
[TD]Product 9[/TD]
[TD] 13,426[/TD]
[TD]Team 3[/TD]
[TD]T2 Own[/TD]
[/TR]
[TR]
[TD]Cust11[/TD]
[TD]Product 11[/TD]
[TD] 14,678[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust12[/TD]
[TD]Product 12[/TD]
[TD] 17,431[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust14[/TD]
[TD]Product 14[/TD]
[TD] 14,932[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust14[/TD]
[TD]Product 14[/TD]
[TD] 17,393[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust15[/TD]
[TD]Product 14[/TD]
[TD] 18,962[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust16[/TD]
[TD]Product 16[/TD]
[TD] 14,647[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust17[/TD]
[TD]Product 17[/TD]
[TD] 16,890[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust18[/TD]
[TD]Product 18[/TD]
[TD] 10,779[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust20[/TD]
[TD]Product 19[/TD]
[TD] 11,129[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust20[/TD]
[TD]Product 19[/TD]
[TD] 10,422[/TD]
[TD]Team 2[/TD]
[TD]T1 Own[/TD]
[/TR]
</tbody>[/TABLE]
Let me know if any further clarification. Your help greatly appreciate ...Thank you
Can anyone help me with below requirement....here is the data
[TABLE="class: outer_border, width: 323, align: left"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product Name[/TD]
[TD]Amount[/TD]
[TD]Contribution by[/TD]
[TD]Final Status[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD]Product 1[/TD]
[TD] 17,991[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust2[/TD]
[TD]Product 2[/TD]
[TD] 19,901[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]Product 3[/TD]
[TD] 18,948[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]Product 3[/TD]
[TD] 10,730[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust5[/TD]
[TD]Product 5[/TD]
[TD] 16,754[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust6[/TD]
[TD]Product 6[/TD]
[TD] 12,324[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust7[/TD]
[TD]Product 7[/TD]
[TD] 11,813[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust8[/TD]
[TD]Product 8[/TD]
[TD] 11,634[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust9[/TD]
[TD]Product 9[/TD]
[TD] 17,742[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust9[/TD]
[TD]Product 9[/TD]
[TD] 13,426[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust11[/TD]
[TD]Product 11[/TD]
[TD] 14,678[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust12[/TD]
[TD]Product 12[/TD]
[TD] 17,431[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust14[/TD]
[TD]Product 14[/TD]
[TD] 14,932[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust14[/TD]
[TD]Product 14[/TD]
[TD] 17,393[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust15[/TD]
[TD]Product 14[/TD]
[TD] 18,962[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust16[/TD]
[TD]Product 16[/TD]
[TD] 14,647[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust17[/TD]
[TD]Product 17[/TD]
[TD] 16,890[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust18[/TD]
[TD]Product 18[/TD]
[TD] 10,779[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust20[/TD]
[TD]Product 19[/TD]
[TD] 11,129[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust20[/TD]
[TD]Product 19[/TD]
[TD] 10,422[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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....
[TABLE="class: outer_border, width: 323"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product Name[/TD]
[TD]Amount[/TD]
[TD]Contribution by[/TD]
[TD]Final Status[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD]Product 1[/TD]
[TD] 17,991[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust2[/TD]
[TD]Product 2[/TD]
[TD] 19,901[/TD]
[TD]Team 2[/TD]
[TD]T2 Own[/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]Product 3[/TD]
[TD] 18,948[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]Product 3[/TD]
[TD] 10,730[/TD]
[TD]Team 1[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust5[/TD]
[TD]Product 5[/TD]
[TD] 16,754[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust6[/TD]
[TD]Product 6[/TD]
[TD] 12,324[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust7[/TD]
[TD]Product 7[/TD]
[TD] 11,813[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust8[/TD]
[TD]Product 8[/TD]
[TD] 11,634[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust9[/TD]
[TD]Product 9[/TD]
[TD] 17,742[/TD]
[TD]Team 2[/TD]
[TD]T2 Own[/TD]
[/TR]
[TR]
[TD]Cust9[/TD]
[TD]Product 9[/TD]
[TD] 13,426[/TD]
[TD]Team 3[/TD]
[TD]T2 Own[/TD]
[/TR]
[TR]
[TD]Cust11[/TD]
[TD]Product 11[/TD]
[TD] 14,678[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust12[/TD]
[TD]Product 12[/TD]
[TD] 17,431[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust14[/TD]
[TD]Product 14[/TD]
[TD] 14,932[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust14[/TD]
[TD]Product 14[/TD]
[TD] 17,393[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust15[/TD]
[TD]Product 14[/TD]
[TD] 18,962[/TD]
[TD]Team 3[/TD]
[TD]T3 Own[/TD]
[/TR]
[TR]
[TD]Cust16[/TD]
[TD]Product 16[/TD]
[TD] 14,647[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust17[/TD]
[TD]Product 17[/TD]
[TD] 16,890[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust18[/TD]
[TD]Product 18[/TD]
[TD] 10,779[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust20[/TD]
[TD]Product 19[/TD]
[TD] 11,129[/TD]
[TD]Team 1[/TD]
[TD]T1 Own[/TD]
[/TR]
[TR]
[TD]Cust20[/TD]
[TD]Product 19[/TD]
[TD] 10,422[/TD]
[TD]Team 2[/TD]
[TD]T1 Own[/TD]
[/TR]
</tbody>[/TABLE]
Let me know if any further clarification. Your help greatly appreciate ...Thank you
Last edited: