Find and record ties

[jgtibbetts]

Hello all,

I keep track of a fantasy golf league and each week I create and import a file with golfer names and winnings. After importing and parsing the file, I insert the team winnings into a row cooresponding to the current week's tournament name down column A and under the cooresponding team name in row B1:AY1. In cell AZ2 and copied down I have the formula:

=INDEX($B$1:$AY$1,MATCH(MAX($B2:$AY2),A2:AY2,0))

which returns the team name with the highest earnings for the week. The problem is it only finds the first instance of the max value and ignores any ties which occasionaly happen. Is there a way to find all team names if there are ties and record them somewhere?

Thanks for any and all help,
Jim T

[daffodil11]

I went with an array that just pulls each of up to a threeway tie. Could easily be altered for more.

[jgtibbetts]

Yikes! That's quite a formula. Let me chew on this for awhile and see if I can understand what you are doing and modify it to work with my needs.

Thanks so much for the help,
Jim T

[daffodil11]

Here's a more attractive option; use one of the many ConcatenationIF User Defined Functions found around the web.

The one below uses the following parameters:
=ConcatIf(criteria range, criteria, return range, delimiter in quotes)

So for a given list of scores in B1:B5, and respective names in A1:A5, you would use:
=ConcatIf(B1:B5,MAX(B1:B5),A1:A5, ", ")

Note: I went with comma-space as the delimiter to make it pretty.
The usage is simpler, even if the code is tougher. But you don't need to know code to use it. I sure as heck don't know what half of it means.

To use the code below, in Excel hit Alt+F11, Insert Module, paste the code below, and close that window.

Call the new custom function with =ConcatIf(

Please Login or Register  to view this content.

[dingdongsilver]

This is how you could do it in Power Pivot, super easy, no code to maintain, etc etc.

[jgtibbetts]

dingdongsilver - Thank you for your quick reply. Your solution would be perfect if I was going to publish the results. It looks very nice. However, I use the results internally for future reference and only I see them.

Jim T

[jgtibbetts]

Thank you, thank you, thank you. That is brilliant. It works perfectly. I can sleep tonight.
Jim T

[jgtibbetts]

Daffodil11 - Thank you, thank you, thank you. That is brilliant. It works perfectly. I can sleep tonight.
Jim T