Calculating Intersection of Multiple Groups

Below is the scoring statistics of the recent 3 games of a certain basketball team. Find out the players whose scores in all games are among the top 5.

Excel

To solve the problem, you need to get the rankings of scores in each game. Therefore, firstly, sort data ascendingly by the primary keyword of game, and descendingly by the secondary keyword of scores. The result is given below:

Categorize and summarize data by the game, and delete the grand total summary row. The result is shown below:

To find out the players whose scores in every game are among the top 5, you can use the following two methods:

1. Method 1: firstly, add a computed column to calculate the rankings of scores in each game. To do so, enter a formula for each game respectively, for example, type the formula =row()-2 in E3, and then drag down the formula to the rest of the cells in E3:E10. The result is shown below:

Then, perform filter:

You can also filter each group of data manually, without adding a computed column. However, the operational efficiency will be much lower.

Copy the filtered data to a new worksheet, and sort it according to the players in ascending order. The result is shown below:

Then, categorize and summarize data by the player to calculate the total number of games where each of the players has scores ranking the top 5:

If the total number of games is 3, it indicates that the scores of the 3 games of this player all rank among the top 5:

With this method, the problem of getting the intersection of sets is converted to counting the occurrences of players in each set. The procedure is rather complicated and you will have to perform it manually. The workload is heavy when the data volume is huge and the number of groups is relatively great.

2. Method 2. After grouping data by the game, you can do the calculation directly with a formula in another area in the grid:

This method has fewer steps, but since no function directly dealing with sets is available in Excel, you will have to try many means to figure it out. Take the above approach as an example. It searches for the value of a set of single cell in another two sets respectively and counts its occurrences to find if the value is one of the members of their intersection. The formula building is difficult, and the more the number of groups increases, the more complicated and error-prone the formula becomes.

It’s very inconvenient to handle intersection and other set operations automatically in Excel because of the lack of corresponding functions. You have to approach the problem in a very roundabout way, which makes the problem solving very complicated. What’s worse, workload will become extremely heavy when there’s a large amount of data with a large number of groups.

esCalc

Firstly, group data by game. The result is shown below:

Then, sort the rows in descending order by scores in each game. By default, the sort operation will be carried out on all homorows in all groups:

Next, perform the filter operation:

In the grouping rows, get the set of the 5 players ranking the top 5 in the current game. To do so, fill the expression ={A3} in E2. The result is shown below:

Get the set of players whose scores of each game are among the top 5. To do so, write the expression ={E2}.isect() in E1. The result is shown below:

esCalc provides thorough support for set operations and enables sorting and filtering after data grouping. That will liberate you from the effort of searching for a roundabout route in order to solve that sort of problem. You can work it out step by step by simply following the business logic. The process is professional and efficient.