Formula or Tool to work out which rows of data is equal to the totals

[Scarbrain]

I have 4 columns of values for different opportunities out of Salesforce

Total Annual Amount - the sum of Total Annual Amount equals the Total Opportunity Amount (there could be multiple rows due to different product categories)

M1 Revenue - is the total amount attributed to M1 - however do not sum as each row is already the total

M3 Revenue - as per M1 notes but = M3

M5 Revenue - as per M1 notes but = M5

Is there a formula or tool to determine which M1-M5 category each Total Annual Amount belongs to?

Sample Attached - the sum of Total Annual for the first three rows = M3, the last row = M1

Could be M1, M3, M5

[apathetichell · Accepted solution]

you could add a primary key. transpose the m1-m5 rows and then join the total amount on the [Value] field. the resulting matched [Name] would be the match. you'd want to bulk convert double to fixed decimal prior to this (probably in a multi-field formula)

or you can just write a formula like if [total annual amount]=[m1] then "m1" elseif [total annual amount] = "m2" then "m2"

elseif [total annual amount] = [m3] then "m3" 

elseif [total annual amount] = [m4] then "m4" else

else "m5" endif

It's not that bad for 5...