I need help with this simple calculation in Excel that I am having difficulty with. I have tried couple of formulas so far and nothing is giving me the correct answer. The formulas are displayed in "MyProduct#1" and "MyProduct#2" columns.

So the "Original table" is what I am working with and the "Desired Table"(second table below the highlighted table) is what I am trying to accomplish.

The original table displays bunch of numbers and some of them are duplicates (highlighted in different colors), but their calc values are different.

I want to look for a match in Col A and, if found one multiply their calc values and display the final result in the "Correct Product" column.

For ex.. 123 is displayed 3 times but its calc value is different all three times, I want to look for corresponding calc values and multiply them.

so the end result for 123 should be 1*1*0 = 0 and that is what I want displayed in rows 4, 7, and 11.

I tried to using the Product(IF) and IF statements in Columns C and D, but none of them are displaying the correct results for me. The desired result is displayed in COl E.

Then I would like to come up with a table (Desired Table)that displays everything from "original table" with correct product and filters out duplicates.

Any help would be greatly appreciated.

Thanks,

Here is the link to the excel file.

https://app.box.com/s/kp25drdrscz6x96298x3vf9gaqogpqnt