Need to fill the table by seggregating data

furi_07

New Member
Joined
Feb 16, 2021
Messages
1
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi ,

Im creating a project where i want to store data of round robin tournament and i have 10 participants which we will play with each other over the period. i have a table where i store match data . and i want another table to show which matches are already played and which are pending for respective captain .

for reference im attaching screenshots of my sheet and desired output table which i want .

im ok with output like Done/pending , True/false, Match date (stored in sheet). match no (stored in same sheet)..
if you are referencing image , i want to fill any of the table and condition should be if captain 1 & captain 2 have faced each other already then it should show in either of table 2 or table 1 whichever is feasible
IMG_20210216_110857.jpg
IMG_20210216_110754.jpg
IMG_20210216_111618.jpg
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Try this:
Table 1 (If result equal 1 then Captain1 won and if 2 Captain 2)
Book1
ABC
1Captain 1Captain 2Matched Won By
2Player1Player12
3Player2Player12
4Player3Player12
5Player4Player11
6Player5Player12
7Player6Player11
8Player7Player12
9Player8Player12
10Player9Player12
11Player10Player12
12Player1Player22
13Player2Player21
14Player3Player21
15Player4Player21
16Player5Player22
17Player6Player22
18Player7Player22
19Player8Player21
20Player9Player22
21Player10Player22
22Player1Player31
23Player2Player32
24Player3Player31
25Player4Player31
26Player5Player31
27Player6Player31
28Player7Player31
29Player8Player32
30Player9Player32
31Player10Player32
32Player1Player42
33Player2Player42
34Player3Player42
35Player4Player42
36Player5Player41
37Player6Player41
38Player7Player42
39Player8Player42
40Player9Player42
41Player10Player41
42Player1Player52
43Player2Player51
44Player3Player51
45Player4Player52
46Player5Player51
47Player6Player52
48Player7Player51
49Player8Player52
50Player9Player51
51Player10Player51
52Player1Player61
53Player2Player61
54Player3Player61
55Player4Player62
56Player5Player62
57Player6Player61
58Player7Player62
59Player8Player61
60Player9Player62
61Player10Player62
62Player1Player72
63Player2Player72
64Player3Player71
65Player4Player72
66Player5Player71
67Player6Player72
68Player7Player71
69Player8Player71
70Player9Player71
71Player10Player72
72Player1Player82
73Player2Player82
74Player3Player82
75Player4Player82
76Player5Player82
77Player6Player81
78Player7Player82
79Player8Player81
80Player9Player82
81Player10Player82
82Player1Player92
83Player2Player91
84Player3Player92
85Player4Player92
86Player5Player92
87Player6Player92
88Player7Player91
89Player8Player91
90Player9Player91
91Player10Player92
92Player1Player101
93Player2Player101
94Player3Player102
95Player4Player102
96Player5Player102
97Player6Player101
98Player7Player102
99Player8Player102
100Player9Player102
101Player10Player102
Sheet2

Table 2 ( The Column A (Home = Captain 1) And Row 2 (Guest = Captain 2).
Result: Captain 1 won means 1 - 0 & Captain 2 won means 0 - 1
Cell Formulas
RangeFormula
B3:K12B3=IF($A3=B$2,"--",IF(INDEX(Sheet2!$C$2:$C$101,MATCH(1,(Sheet2!$A$2:$A$101=$A3)*(Sheet2!$B$2:$B$101=B$2),0))=1,"1 - 0","0 - 1"))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top