Calculating Top 3 Answers in Three Columns

[chicagoneil]

Greetings,

I've been using Filemaker for the past month or so and have had success in accomplishing most of the tasks that are necessary for the analysis of a recent survey project. However, I have come across a problem whose only answer is quite cumbersome to implement. Perhaps somebody has a better way...

In my survey, we asked respondents to give us their top three service providers from a list of 41 companies. The companies were entered in as text in three fields (Company1, Company2, Company3) each with value corresponding to the amount spent with that company (Company1Spend, Company2Spend, Company3Spend). The problem comes in with calculating who the top 3 companies are for each and how much they spend. All I can figure out to do would to be 41 seperate fields with if statements that review all three columns for that particular company name, add its spend and then do a summary afterwards. Any other approaches would be tremendously appreciated.

Thanks in advance.