Excel: Calculations with two variables, that both share the same ID-CodePudding

I have been working on a little project in which I analyze some data from a game that I play. My dataset looks like this:

As you can see, it consists of:

Match ID
Map the match was played on
Team name
First pick, second pick and third pick (characters/players)
Points the teams won from this match
What side they played on (A or B)
Who won
Whether they're in the top 64 teams

Currently I am trying to analyze how certain picks perform against other picks. For example, I would like to see how the Xelor first pick (cell D2) performs against all other first picks. To do this, I would need to count the amount of times the Xelor first pick played against all other first pick, and how many times the Xelor pick won. I don't have any problems doing that, but the catch is that I need to make sure I only compare the Xelor first picks with other first picks from the same match (same match ID). For example, I would compare the Xelor first pick (D2) vs the Steamer first pick (D3), as they share the same match ID.

I came up with a messy solution earlier with simple formulas, but it made for a table that had no data every other row, which resulted in some problems analyzing the data. I am now struggling with the Index and Match functions to make a pretty table for my needs, but I am having a hard time.

If anyone could give me a hand on how to do this, or has any clever ideas on how to analyze all picks vs other picks, let me know!

CodePudding user response：

You could try something like this in M2 cell:

=IF(L2="","",COUNTIFS(TB_GAMES[W/L/D],"Win", 
 TB_GAMES[Pick 1],L2,TB_GAMES[Match],$K$2))

Then you can expand the formula down.

In L column you have the unique values from users given the Match (K2) and the Pick 1 column values.

=UNIQUE(FILTER(TB_GAMES[Pick 1], TB_GAMES[Match]=K2))

CodePudding user response：

So, it turns out that both the Unique function and the Xlookup functions made this an easy problem to solve.

First, I made a new column showing just the unique match ID values:

=UNIQUE(A:A)

Then, next to that column I looked up the first pick of the A side team using Xlookup:

=XLOOKUP(M2;A:A;C:C;;0;1)

I then did the same in another column for the team on the other side using an inverse search direction:

=XLOOKUP(M2;A:A;C:C;;0;-1)

Lastly, to see which of the two first picks won, I used this formula in a fourth column:

=IF(XLOOKUP(M2;A:A;H:H;;0;1)="Win";N2;O2)

This resulted in the following table (M:P):

Thanks for the help, David!