transforming and organizing results in tournament

[dworkin]

Hi,
I'm organizing a tournament and i need to convert data from one sheet to another.
original sheet:

round

table

p1 ID

p1 name

p1 score

p2 ID

p2 name

p2 score

p3 ID

p3 name

p3 score

p4 ID

p4 name

p4 score

1​

1​

1​

Trinkl Matthias

15000,0​

2​

Diviš Martin

5000,0​

3​

Diviš Carrie

-5000,0​

4​

Beck Daniel

-15000,0​

1​

2​

5​

Hoskovec David

12000,0​

6​

Bílek Lukáš

6000,0​

7​

Konderlová Jana

-12000,0​

8​

Berkman Tadeáš

-6000,0​

1​

3​

9​

Trmal Vojtěch

9000,0​

10​

Duraković Zijad

4000,0​

11​

Kruse Markus

-5000,0​

12​

Frings Jonas

-8000,0​

1​

4​

13​

Rappe Marco

8000,0​

14​

Schlich Manuel

2000,0​

15​

Völker Marcus

-8300,0​

16​

Hahn Timur

-1700,0​

1​

5​

17​

Kammer Stefanie

7000,0​

18​

Laube Waldemar

1500,0​

19​

Rus Joaquin Derrac

-7200,0​

20​

Huynh Joe-Calberson

-1300,0​

1​

6​

21​

Trepinska Karolina

6000,0​

22​

Debrun Luc

7500,0​

23​

Potmeer Pieter

-20100,0​

24​

Arendarski Bartosz

6600,0​

1​

7​

25​

Zieliński Tomasz

5000,0​

26​

Chabelska Katarzyna

-5800,0​

27​

Wojtasik Pawel

12500,0​

28​

Khezam Rene

-11700,0​

tournament goes for 8 rounds - so number in column round changes.
I need a function, that would pick the scores and put them in this table in another sheet in the corresponding round for each player (as shown)

Surname

Name

Number

Country

EMA

Total

Round 1

Round 2

Round 3

Round 4

Round 5

Round 6

Round 7

Round 8

Trinkl

Matthias

1​

AUT

01000108

0​

1500

Diviš

Martin

2​

CZE

22000003

0​

5000

Diviš

Carrie

3​

CZE

22000013

0​

-5000

Beck

Daniel

4​

CZE

22000022

0​

-15000

table is bigger with all the names of course.
Is there an easy sollution for this? I tried combination of IF and VLOOKUP, but it doesn't work.
Thanks for any ideas

 

[lrobbo314]

Hopefully this will get you part of the way there. It's kind of confusing since your expected results have 'Country' and 'EMA' but those columns aren't in your original dataset. This solution uses Power Query.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	round	table	p1 ID	p1 name	p1 score	p2 ID	p2 name	p2 score	p3 ID	p3 name	p3 score	p4 ID	p4 name	p4 score
2	1	1	1	Trinkl Matthias	-18,000	2	Diviš Martin	-18,000	3	Diviš Carrie	-18,000	4	Beck Daniel	-18,000
3	1	2	5	Hoskovec David	15,000	6	Bílek Lukáš	-18,000	7	Konderlová Jana	15,000	8	Berkman Tadeáš	-18,000
4	1	3	9	Trmal Vojtěch	13,000	10	Duraković Zijad	-18,000	11	Kruse Markus	13,000	12	Frings Jonas	-18,000
5	1	4	13	Rappe Marco	12,000	14	Schlich Manuel	-18,000	15	Völker Marcus	12,000	16	Hahn Timur	-18,000
6	1	5	17	Kammer Stefanie	-2,000	18	Laube Waldemar	-18,000	19	Rus Joaquin Derrac	-2,000	20	Huynh Joe-Calberson	-18,000
7	1	6	21	Trepinska Karolina	-16,000	22	Debrun Luc	-18,000	23	Potmeer Pieter	-16,000	24	Arendarski Bartosz	-18,000
8	1	7	25	Zieliński Tomasz	3,000	26	Chabelska Katarzyna	-18,000	27	Wojtasik Pawel	3,000	28	Khezam Rene	-18,000
9	2	1	1	Trinkl Matthias	8,000	2	Diviš Martin	-18,000	3	Diviš Carrie	8,000	4	Beck Daniel	-18,000
10	2	2	5	Hoskovec David	-9,000	6	Bílek Lukáš	-18,000	7	Konderlová Jana	-9,000	8	Berkman Tadeáš	-18,000
11	2	3	9	Trmal Vojtěch	-1,000	10	Duraković Zijad	-18,000	11	Kruse Markus	-1,000	12	Frings Jonas	-18,000
12	2	4	13	Rappe Marco	-11,000	14	Schlich Manuel	-18,000	15	Völker Marcus	-11,000	16	Hahn Timur	-18,000
13	2	5	17	Kammer Stefanie	15,000	18	Laube Waldemar	-18,000	19	Rus Joaquin Derrac	15,000	20	Huynh Joe-Calberson	-18,000
14	2	6	21	Trepinska Karolina	-4,000	22	Debrun Luc	-18,000	23	Potmeer Pieter	-4,000	24	Arendarski Bartosz	-18,000
15	2	7	25	Zieliński Tomasz	18,000	26	Chabelska Katarzyna	-18,000	27	Wojtasik Pawel	18,000	28	Khezam Rene	-18,000
16	3	1	1	Trinkl Matthias	18,000	2	Diviš Martin	-18,000	3	Diviš Carrie	18,000	4	Beck Daniel	-18,000
17	3	2	5	Hoskovec David	9,000	6	Bílek Lukáš	-18,000	7	Konderlová Jana	9,000	8	Berkman Tadeáš	-18,000
18	3	3	9	Trmal Vojtěch	-12,000	10	Duraković Zijad	-18,000	11	Kruse Markus	-12,000	12	Frings Jonas	-18,000
19	3	4	13	Rappe Marco	-20,000	14	Schlich Manuel	-18,000	15	Völker Marcus	-20,000	16	Hahn Timur	-18,000
20	3	5	17	Kammer Stefanie	-1,000	18	Laube Waldemar	-18,000	19	Rus Joaquin Derrac	-1,000	20	Huynh Joe-Calberson	-18,000
21	3	6	21	Trepinska Karolina	9,000	22	Debrun Luc	-18,000	23	Potmeer Pieter	9,000	24	Arendarski Bartosz	-18,000
22	3	7	25	Zieliński Tomasz	-5,000	26	Chabelska Katarzyna	-18,000	27	Wojtasik Pawel	-5,000	28	Khezam Rene	-18,000
23	4	1	1	Trinkl Matthias	-19,000	2	Diviš Martin	-18,000	3	Diviš Carrie	-19,000	4	Beck Daniel	-18,000
24	4	2	5	Hoskovec David	-20,000	6	Bílek Lukáš	-18,000	7	Konderlová Jana	-20,000	8	Berkman Tadeáš	-18,000
25	4	3	9	Trmal Vojtěch	20,000	10	Duraković Zijad	-18,000	11	Kruse Markus	20,000	12	Frings Jonas	-18,000
26	4	4	13	Rappe Marco	14,000	14	Schlich Manuel	-18,000	15	Völker Marcus	14,000	16	Hahn Timur	-18,000
27	4	5	17	Kammer Stefanie	-11,000	18	Laube Waldemar	-18,000	19	Rus Joaquin Derrac	-11,000	20	Huynh Joe-Calberson	-18,000
28	4	6	21	Trepinska Karolina	3,000	22	Debrun Luc	-18,000	23	Potmeer Pieter	3,000	24	Arendarski Bartosz	-18,000
29	4	7	25	Zieliński Tomasz	-2,000	26	Chabelska Katarzyna	-18,000	27	Wojtasik Pawel	-2,000	28	Khezam Rene	-18,000
30
31	ID	NAME	1	2	3	4	Total
32	1	Trinkl Matthias	-18,000	8,000	18,000	-19,000	-11,000
33	2	Diviš Martin	-18,000	-18,000	-18,000	-18,000	-72,000
34	3	Diviš Carrie	-18,000	8,000	18,000	-19,000	-11,000
35	4	Beck Daniel	-18,000	-18,000	-18,000	-18,000	-72,000
36	5	Hoskovec David	15,000	-9,000	9,000	-20,000	-5,000
37	6	Bílek Lukáš	-18,000	-18,000	-18,000	-18,000	-72,000
38	7	Konderlová Jana	15,000	-9,000	9,000	-20,000	-5,000
39	8	Berkman Tadeáš	-18,000	-18,000	-18,000	-18,000	-72,000
40	9	Trmal Vojtěch	13,000	-1,000	-12,000	20,000	20,000
41	10	Duraković Zijad	-18,000	-18,000	-18,000	-18,000	-72,000
42	11	Kruse Markus	13,000	-1,000	-12,000	20,000	20,000
43	12	Frings Jonas	-18,000	-18,000	-18,000	-18,000	-72,000
44	13	Rappe Marco	12,000	-11,000	-20,000	14,000	-5,000
45	14	Schlich Manuel	-18,000	-18,000	-18,000	-18,000	-72,000
46	15	Völker Marcus	12,000	-11,000	-20,000	14,000	-5,000
47	16	Hahn Timur	-18,000	-18,000	-18,000	-18,000	-72,000
48	17	Kammer Stefanie	-2,000	15,000	-1,000	-11,000	1,000
49	18	Laube Waldemar	-18,000	-18,000	-18,000	-18,000	-72,000
50	19	Rus Joaquin Derrac	-2,000	15,000	-1,000	-11,000	1,000
51	20	Huynh Joe-Calberson	-18,000	-18,000	-18,000	-18,000	-72,000
52	21	Trepinska Karolina	-16,000	-4,000	9,000	3,000	-8,000
53	22	Debrun Luc	-18,000	-18,000	-18,000	-18,000	-72,000
54	23	Potmeer Pieter	-16,000	-4,000	9,000	3,000	-8,000
55	24	Arendarski Bartosz	-18,000	-18,000	-18,000	-18,000	-72,000
56	25	Zieliński Tomasz	3,000	18,000	-5,000	-2,000	14,000
57	26	Chabelska Katarzyna	-18,000	-18,000	-18,000	-18,000	-72,000
58	27	Wojtasik Pawel	3,000	18,000	-5,000	-2,000	14,000
59	28	Khezam Rene	-18,000	-18,000	-18,000	-18,000	-72,000
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RC = Table.RemoveColumns(Source,{"table"}),
    Type = Table.TransformColumnTypes(RC,{{"round", Int64.Type}, {"p1 ID", Int64.Type}, {"p1 score", Int64.Type}, {"p2 score", Int64.Type}, {"p2 ID", Int64.Type}, {"p3 ID", Int64.Type}, {"p3 score", Int64.Type}, {"p4 ID", Int64.Type}, {"p4 score", Int64.Type}}),
    UP = Table.UnpivotOtherColumns(Type, {"round"}, "Attribute", "Value"),
    Trim = Table.TransformColumns(UP,{{"Attribute", each Text.Upper(Text.Split(_," "){1})}}),
    Index = Table.AddIndexColumn(Trim, "Index", 0, 1, Int64.Type),
    aCNT = List.Count(List.Distinct(Index[Attribute])),
    IntDiv = Table.TransformColumns(Index, {{"Index", each Number.IntegerDivide(_, aCNT), Int64.Type}}),
    Pivot = Table.Pivot(IntDiv, List.Distinct(IntDiv[Attribute]), "Attribute", "Value"),
    RemoveIndex = Table.RemoveColumns(Pivot,{"Index"}),
    Pivot1 = Table.Pivot(Table.TransformColumnTypes(RemoveIndex, {{"round", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(RemoveIndex, {{"round", type text}}, "en-US")[round]), "round", "SCORE", List.Sum),
    Total = Table.AddColumn(Pivot1, "Total", each List.Sum(List.Skip(Record.FieldValues(_),Table.ColumnCount(RemoveIndex)-aCNT+1)))
in
    Total

 

[dworkin]

Country and EMA come from a different sheet as full identifiers for the players.
The results table is shorter to be more clear to read and write into. players names in "score" sheet are VLOOKUP from another sheet with some added cells to make sure there are no mistakes.
the "results" sheet is then a compilation of data from about 3 or 4 other sheets.
Your power query looks elegant, but I don't know how to put it in function - I don't know much about functions in Excel and I'm using only a couple of them on regular basis.
Do you have another possible solution for this that does not include Power query?
One more thing, the amount of tables per round can change given the amount of players in the tournament, but will never change throughout.

 

[dworkin]

Is there a possibility of using Named areas and VLOOKUP function?