Forum Discussion
Solved
Unique Values Across Two Columns Using SUMMARIZE
Hi I have a table of football fixtures ('All Results') which contains a home team, away team and the number of goals scored by each, see below: The full data set is much bigger than that, ...
Hi,
PBI file attached.
Hope this helps.
Below is a sample of the data I am usung. A mix of some teams playing home and away, some just one match.
| Div | Date | Time | HomeTeam | AwayTeam | Home | Away |
| E2 | 07 August 2025 | 1899-12-30 20:00:00 | Port Vale | Cardiff | 0 | 0 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | AFC Wimbledon | Lincoln | 2 | 0 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | Barnsley | Burton | 3 | 2 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | Bolton | Plymouth | 2 | 0 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | Exeter | Blackpool | 4 | 1 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | Leyton Orient | Wigan | 2 | 0 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | Mansfield | Doncaster | 1 | 2 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | Northampton | Bradford | 0 | 0 |
| E2 | 09 August 2025 | 1899-12-30 12:30:00 | Peterboro | Luton | 0 | 2 |
| E2 | 09 August 2025 | 1899-12-30 12:30:00 | Reading | Huddersfield | 0 | 2 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | Stevenage | Rotherham | 1 | 0 |
| E2 | 09 August 2025 | 1899-12-30 15:00:00 | Wycombe | Stockport | 1 | 2 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Barnsley | Bolton | 1 | 1 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Blackpool | Huddersfield | 3 | 2 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Bradford | Luton | 2 | 1 |
| E2 | 16 August 2025 | 1899-12-30 12:30:00 | Burton | Port Vale | 0 | 0 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Cardiff | Rotherham | 3 | 0 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Doncaster | Wycombe | 1 | 1 |
| E2 | 16 August 2025 | 1899-12-30 12:30:00 | Exeter | Mansfield | 1 | 2 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Leyton Orient | Stockport | 2 | 2 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Lincoln | Plymouth | 3 | 2 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Reading | AFC Wimbledon | 1 | 2 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Stevenage | Northampton | 2 | 0 |
| E2 | 16 August 2025 | 1899-12-30 15:00:00 | Wigan | Peterboro | 2 | 0 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | AFC Wimbledon | Cardiff | 0 | 1 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Huddersfield | Doncaster | 2 | 0 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Luton | Wigan | 1 | 0 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Mansfield | Blackpool | 2 | 0 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Northampton | Lincoln | 0 | 1 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Peterboro | Barnsley | 0 | 1 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Plymouth | Leyton Orient | 0 | 1 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Port Vale | Stevenage | 1 | 2 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Stockport | Bradford | 1 | 2 |
| E2 | 19 August 2025 | 1899-12-30 19:45:00 | Wycombe | Exeter | 0 | 1 |
| E2 | 20 August 2025 | 1899-12-30 20:00:00 | Bolton | Reading | 1 | 1 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | AFC Wimbledon | Barnsley | 2 | 0 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Bolton | Lincoln | 1 | 1 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Huddersfield | Stevenage | 1 | 0 |
| E2 | 23 August 2025 | 1899-12-30 12:30:00 | Luton | Cardiff | 0 | 1 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Mansfield | Leyton Orient | 4 | 1 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Northampton | Exeter | 2 | 0 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Peterboro | Bradford | 1 | 1 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Plymouth | Blackpool | 1 | 0 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Port Vale | Doncaster | 0 | 1 |
| E2 | 23 August 2025 | 1899-12-30 12:30:00 | Rotherham | Wigan | 2 | 2 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Stockport | Burton | 2 | 1 |
| E2 | 23 August 2025 | 1899-12-30 15:00:00 | Wycombe | Reading | 2 | 2 |
| G1 | 23 August 2025 | 1899-12-30 18:00:00 | Aris | Volos NFC | 2 | 0 |
| G1 | 23 August 2025 | 1899-12-30 18:00:00 | Olympiakos | Asteras Tripolis | 2 | 0 |
| G1 | 23 August 2025 | 1899-12-30 20:00:00 | Panetolikos | Atromitos | 0 | 2 |
| G1 | 24 August 2025 | 1899-12-30 18:15:00 | AEK | Panserraikos | 2 | 0 |
| G1 | 24 August 2025 | 1899-12-30 19:00:00 | PAOK | Larisa | 1 | 0 |
| G1 | 25 August 2025 | 1899-12-30 17:30:00 | Levadeiakos | Kifisia | 3 | 2 |
The beow is the summary table I am looking to contstruct:
| Team | Played | Home Goals | Away Goals | Home Conceded | Away Conceded |
| Port Vale | 4 | 1 | 0 | 3 | 0 |
| AFC Wimbledon | 4 | 4 | 2 | 1 | 1 |
| Barnsley | 4 | 4 | 1 | 3 | 2 |
| Bolton | 4 | 4 | 1 | 2 | 1 |
| Exeter | 4 | 5 | 1 | 3 | 2 |
| Leyton Orient | 4 | 4 | 2 | 2 | 4 |
| Mansfield | 4 | 7 | 2 | 3 | 1 |
| Northampton | 4 | 2 | 0 | 1 | 2 |
| Peterboro | 4 | 1 | 0 | 4 | 2 |
| Reading | 4 | 1 | 3 | 4 | 3 |
| Stevenage | 4 | 3 | 2 | 0 | 2 |
| Wycombe | 4 | 3 | 1 | 5 | 1 |
| Blackpool | 4 | 3 | 1 | 2 | 7 |
| Bradford | 4 | 2 | 3 | 1 | 2 |
| Burton | 3 | 0 | 3 | 0 | 5 |
| Cardiff | 4 | 3 | 2 | 0 | 0 |
| Doncaster | 4 | 1 | 3 | 1 | 3 |
| Lincoln | 4 | 3 | 2 | 2 | 3 |
| Wigan | 4 | 2 | 2 | 0 | 5 |
| Huddersfield | 4 | 3 | 4 | 0 | 3 |
| Luton | 4 | 1 | 3 | 1 | 2 |
| Plymouth | 4 | 1 | 2 | 1 | 5 |
| Stockport | 4 | 3 | 4 | 3 | 3 |
| Rotherham | 3 | 2 | 0 | 2 | 4 |
| Aris | 1 | 2 | 0 | 0 | 0 |
| Olympiakos | 1 | 2 | 0 | 0 | 0 |
| Panetolikos | 1 | 0 | 0 | 2 | 0 |
| AEK | 1 | 2 | 0 | 0 | 0 |
| PAOK | 1 | 1 | 0 | 0 | 0 |
| Levadeiakos | 1 | 3 | 0 | 2 | 0 |
| Volos NFC | 1 | 0 | 0 | 0 | 2 |
| Asteras Tripolis | 1 | 0 | 0 | 0 | 2 |
| Atromitos | 1 | 0 | 2 | 0 | 0 |
| Panserraikos | 1 | 0 | 0 | 0 | 2 |
| Larisa | 1 | 0 | 0 | 0 | 1 |
| Kifisia | 1 | 0 | 2 | 0 | 3 |
Hi,
PBI file attached.
Hope this helps.
Excellent, that seems to do the job. Thanks.
You are welcome.