Comparitive analysis - compare player performance in two or more matches

Dear Friends,

Now this is becoming an headache for me. I am posting here but dont know whether I will be able to explain it properly.

I have some data that I posted below which shows player performance in a cricket tournament called State Premier League. First column is the Match number and next columns are names of the players. Instead of putting names, I had put their Jersey number for identification. The first level of the league of 21 matches are over and based on that, I wish to narrow down few performing players for next level. It has been observed that when a group of three or more players play together then they perform well and hence I want to find a group of people who have performed well in two or more matches.

It is easier for me to rank individual players and give them rank but I want to rank a group of players where three or more player together performed well and for that I need to have a comparitive analysis where I compare Match#1 with Match#2 and find out if there are 3 or more common players then move on and compare Match#1 and Match#3 and do this until we reach comparing Match#20 and Match#21. When I compared Match performance manually, I found players with Jersey # 24, 25, 66, 75 appeared twice together in Match # 4 and 8. Similarly, I found three groups of players who were common in other matches.

I have tried several formula based solution with/without arrays with/without helper columns and tried vba too. All my formulas failed and I did not put a good logic to write a vba code coz I could not think of an efficient logic for loops and ifs. Appreciate if someone could help.

Note: Out of 11 players, I have shortlisted 5 performing players for every match. In future, I may even shorlist 6 or more. The first level of the league where 21 matches are played could also change based on tournament planning so that the first level can also be less or more than 21 matches hence I would want to make provision for that too.

Here is the comparitive analysis (it could be more, manually I found only 3 instances)
compare match 4 and match 8, 4 players with jersey # 24 25 66 75 were common
compare match 5 and match 19, 3 players with jersey # 29 52 57 were common
compare match 13 and match 15, 3 players with jersey # 18 29 83 were common

Here is the data
Match# 01 : 82 90 1 68 61
Match# 02 : 9 22 52 88 10
Match# 03 : 47 8 40 34 82
Match# 04 : 75 25 66 35 24
Match# 05 : 29 57 43 52 72
Match# 06 : 89 37 35 20 41
Match# 07 : 3 36 49 9 90
Match# 08 : 66 84 24 25 75
Match# 09 : 3 49 65 4 11
Match# 10 : 35 4 29 67 55
Match# 11 : 40 92 51 9 42
Match# 12 : 52 75 53 14 66
Match# 13 : 41 34 83 18 29
Match# 14 : 84 39 20 40 86
Match# 15 : 90 1 18 83 29
Match# 16 : 81 36 55 92 13
Match# 17 : 41 69 59 88 70
Match# 18 : 77 12 2 86 93
Match# 19 : 65 57 52 29 38
Match# 20 : 90 60 41 65 48
Match# 21 : 92 59 58 7 45

Eagerly awaiting for some assistance.

Thank you !

Originally Posted by CAntosh

Well... I don't know if it's the most efficient solution, but try the following formula instead

Thank you once again.

This has solved my problem to a great extent. Most it is now automated but little bit of manual intervention is still there. In your attachment, if you see cell I5 and cell I9, it is a duplicate entry as all the players are same, it is just the date which is different and therefore in my expected result, I had shown it as only one entry with both the dates in one single entry so that I dont need to compare results to find duplicates.

I dont know if I am explaining it properly but it would have been better if everything goes in different column so that I dont have to go and use the Text-To-Column feature.In my previous posts, I wrote, I expect a result like this, Match#4 Match#8, 4 players, 24, 25, 66, 75, I actually wanted them in different columns (Match#4 in a column, Match#8 in the next corresponding column, 4 Players in the next and the next jersey numbers in the next corresponding columns.

I also spoke about making provisions for more than 21 matches in a league and if I shortlist more than 5 players, if I want to change 5 players to 6 players (having 6 jersey numbers in my file instead of 5, then it is becoming a bit tough task to alter the big formula especially when I need to select more than 4 players if my data had 6 shortlisted players).

Is there any way this can be done using a macro/vba?

Originally Posted by Kaper

Note that I added also checking three-matches performance.
If not needed, could be easily removed.
If you like it - you could experiment with 4 matches in common (just 2 variables added, and code copied again wit 2 more loops).

Absolutely wonderful! I wonder if my processor is fast or your code or rather the logic
You have made my day. Since past some time I am trying figure out how the code is working which is a bit difficult for me because lots of loops involved. I am trying to understand though !

Wanted to ask a couple of questions based on the provision I spoke about in my first post:
1. In the above example, the code will list all common players whether they are 4 players, 3 players or 2 players. Can we limit this to 4 or more so that only those many groups are listed in the result? Also this criteria of 4 or more if it can be based on user input? The user input can be picked from a cell lets say C1.
For example, if I type 4 in cell C1, the code will list only those matches where 4 or more common players played together.

Similarly, in the next part of the code (3 matches is counted) also it must list only 3 matches where 4 or more common players played together.

2. What changes I need to make in the code if I increase the players from 5 to 6? and want to list 4 or more common players played together which can be taken as an input from cell C1.

Appreciate if you could answer my questions. Meanwhile I am spending time on the code logic for my understanding.

Thanks a lot

I have answered my own question after thoroughly reading and understanding the code. I have completely understood the logic you have used in this code. I tried to increase the players (pl_per_match) from 5 players to 6 players and then ran the code in a way that it would list only those where more than 2 players played together (If current_match >= 3) and it worked well.

Then I wanted to make one more group (loop) where performance in 4 matches is counted. That means, every match would be compared with 3 other matches so in all 4 matches. So besides For k, For m, For p, I would need one more loop, For q. Here is where I am getting stuck. May I request you to generate one more loop where performance in 4 matches is counted?

Thanking you in anticipation

Originally Posted by Kaper

I believe, you can add 4th loop by yourself*. And what a satisfaction when it's done (OK, I know that before it works, it is mostly frustration :-P, but when finally achieved - it's a real pleasure)


and once you do it - publish it here, may be somebody will search for such a solution :-)

Thank you for putting it this way which will give me an opportunity to do it myself. Will work out this weekend and publish once its done !

Originally Posted by sabha

Will work out this weekend and publish once its done !

phew!

I think I did it right. Request you to have a look at it. I had to do a lot of debug.prints to get it right.

Please Login or Register  to view this content.

Originally Posted by Kaper

Looks fine!

Have you tested it with "prepared data" like



I'm writing from the tablet and have no option to run VBA here, otherwise, I'd test it myself.
But as I wrote above - looks right, and I'm proud of you :-).

Yes I tested and its looked correct but I will test it once again, do it manually and then compare both automatic and manual work to ensure its 100% correct. Thank you so much for your kind assistance and guidance. God bless !