How to combine totals for a sheet with multiple entries for the same thing

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
164
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have a large sheet with soccer players & the number of goals they have scored each season (each column is 1999-2000, 2001-2002, etc. up to 2019-2020). The players names are often duplicated down about 3500 rows of data. How can I get a single row count of all their goals?

For example:

Row/ColumnABCDE
1Player99-0000-0101-02...
2Raul0700
3David Beckham
1822Raul0030
2455Raul00012

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,803
.
If I understand correctly, you are using 228 columns to track all the years/months of goals ?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,451
with Power Query

- select column: Player
- unpivot other columns
- group column Player with sum Value column
 

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
164
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
.
If I understand correctly, you are using 228 columns to track all the years/months of goals ?

It is only 22 columns. 21 seasons + 1 player column.
 

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
164
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

with Power Query

- select column: Player
- unpivot other columns
- group column Player with sum Value column


Thanks that seems to help, but one other problem is that I have a lot of data where the player didn't score any goals for a particular season. Is it possible to remove or filter these results in Power Query to exclude the 0's in each season? I have also attached a sheet with these figures:

https://drive.google.com/file/d/1uFGhksxeDhWSfSOeYHVPDxUHzC8u-H80/view?usp=sharing
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,451

ADVERTISEMENT

ok, got it

but I need to remove all (value) from column goals, is that ok for you ? or you need these values for something more?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,451
here is table (217 rows) but there is no duplicates

SpelerGoalsPen
Raúl
7​
0​
Lee Bowyer
6​
0​
Giovane Élber
6​
0​
Iván Helguera
6​
0​
Jardel
6​
0​
Rivaldo
6​
1​
Paul Scholes
6​
1​
Marco Simone
6​
1​
Nicolas Anelka
5​
0​
Christian
5​
0​
Filippo Inzaghi
5​
1​
Frode Johnsen
5​
0​
Juan Sánchez
5​
0​
Claudio López
5​
0​
Luís Figo
5​
3​
Walter Pandiani
5​
0​
Tomasz Radzinski
5​
0​
Mehmet Scholl
5​
1​
Teddy Sheringham
5​
0​
Andriy Shevchenko
5​
3​
Alan Smith
5​
0​
Diego Alonso
4​
0​
Andy Cole
4​
0​
Guti
4​
0​
Thierry Henry
4​
0​
José Mari
4​
0​
Laurent Leroy
4​
0​
Marcao
4​
0​
Steve Marlet
4​
0​
Morientes
4​
0​
Roberto Carlos
4​
0​
Markus Schopp
4​
0​
Sonny Anderson
4​
1​
Alin Stoica
4​
0​
Egor Titov
4​
0​
Mark Viduka
4​
0​
John Carew
3​
0​
Djalminha
3​
3​
Giovanni
3​
0​
Ian Harte
3​
1​
Simone Inzaghi
3​
0​
Jens Jeremies
3​
0​
Jan Koller
3​
0​
Pavel Nedvìd
3​
0​
Shabani Nonda
3​
0​
Paulo Sérgio
3​
0​
Laurent Robert
3​
1​
Sá Pinto
3​
1​
Giovanni van Bronckhorst
3​
0​
Andriy Vorobey
3​
0​
Sergey Yuran
3​
0​
Demetrio Albertini
2​
0​
André Cruz
2​
0​
Roberto Ayala
2​
0​
Michael Ballack
2​
0​
Baraja
2​
0​
Angelos Basinas
2​
2​
Oliver Bierhoff
2​
0​
Thomas Brdaric
2​
0​
Francesco Coco
2​
0​
Hernán Crespo
2​
0​
Ümit Davala
2​
2​
Georgi Demetradze
2​
0​
Diego Tristán
2​
0​
Ahmet Dursun
2​
0​
Predrag Ðorðeviæ
2​
1​
Christer George
2​
0​
Ryan Giggs
2​
0​
Kily González
2​
0​
Yannis Goumas
2​
0​
Sidney Govou
2​
0​
Mario Haas
2​
0​
Gheorghe Hagi
2​
0​
Denis Irwin
2​
2​
Carsten Jancker
2​
0​
Jiøí Jarošík
2​
0​
Bjorn Johansen
2​
0​
Nwankwo Kanu
2​
0​
Martin Keown
2​
0​
Patrick Kluivert
2​
0​
Pierre Laigle
2​
0​
Freddie Ljungberg
2​
0​
Luis Enrique
2​
0​
Mehdi Mahdavikia
2​
0​
Roy Makaay
2​
0​
Dominic Matteo
2​
0​
Mendieta
2​
1​
Siniša Mihajloviæ
2​
1​
Michael Mols
2​
0​
Noureddine Naybet
2​
0​
Ray Parlour
2​
0​
Adil Ramzi
2​
0​
Fabrizio Ravanelli
2​
0​
Róbson
2​
0​
Tomáš Rosický
2​
0​
Hasan Salihamidžiæ
2​
0​
Hasan ªaº
2​
0​
Maksim Shatskikh
2​
0​
Roar Strand
2​
0​
Sylvinho
2​
0​
Hakan Ünsal
2​
0​
Mark van Bommel
2​
0​
Víctor
2​
0​
Krzysztof Warzycha
2​
0​
Tony Yeboah
2​
0​
Gennadiy Zubov
2​
1​
Bülent Akin
1​
0​
Jörg Albertz
1​
0​
Jimmy Algerino
1​
0​
Alvaro Santos
1​
0​
Jocelyn Angloma
1​
0​
Serhiy Atelkin
1​
0​
Aleksey Bakharev
1​
0​
Eirik Bakke
1​
0​
Sergej Barbarez
1​
0​
Roberto Baronio
1​
0​
Oleksiy Belik
1​
0​
Valentin Belkevich
1​
0​
Orjan Berg
1​
0​
Dennis Bergkamp
1​
0​
Artyom Bezrodny
1​
0​
Nicolas Bonnal
1​
0​
Wilfred Bouma
1​
0​
Arnold Bruggink
1​
0​
Hans Jörg Butt
1​
1​
Nicky Butt
1​
0​
Capone
1​
0​
Phillip Cocu
1​
0​
Pablo Contreras
1​
0​
Costinha
1​
0​
Bertrand Crasson
1​
0​
Frank de Boer
1​
0​
Ronald de Boer
1​
0​
Frédéric Déhu
1​
0​
Didier Dheedene
1​
0​
Aruna Dindane
1​
0​
Lee Dixon
1​
0​
Billy Dodds
1​
0​
Edmilson
1​
0​
Stefan Effenberg
1​
1​
Talal El-Karkouri
1​
0​
Pontus Farnerud
1​
0​
Giuseppe Favalli
1​
0​
Rio Ferdinand
1​
0​
Marc-Vivien Foé
1​
0​
Gabri
1​
0​
Dainius Gleveckas
1​
0​
Bart Goor
1​
0​
Guerino Gottardi
1​
0​
Andriy Gusin
1​
0​
Tayfur Havutçu
1​
1​
Thomas Helveg
1​
0​
Hierro
1​
0​
Michal Horòák
1​
0​
Darren Huckerby
1​
0​
Jesper Jansson
1​
0​
Daniel Jensen
1​
0​
Kakha Kaladze
1​
0​
Andrey Kanchelskis
1​
0​
Giorgios Karagounis
1​
0​
Suat Kaya
1​
0​
Roy Keane
1​
0​
Mateja Kežman
1​
0​
Ulf Kirsten
1​
0​
Tomica Kocijan
1​
0​
Bülent Korkmaz
1​
0​
Niko Kovaè
1​
0​
Darko Kovaèeviæ
1​
0​
Vladimír Labant
1​
1​
Lauren
1​
0​
Leonardo
1​
0​
Thomas Linke
1​
0​
Peter Luccin
1​
0​
Theo Lucius
1​
0​
Claude Makélélé
1​
0​
Steed Malbranque
1​
0​
Kenny Miller
1​
0​
Giorgios Nasiopoulos
1​
0​
Oliver Neuville
1​
0​
Pascal Nouma
1​
0​
Peter Ofori-Quaye
1​
0​
Jay Jay Okocha
1​
0​
André Ooijer
1​
0​
Andrej Panadiæ
1​
0​
Dmytro Parfyonov
1​
1​
Paulo Sousa
1​
0​
Ergün Penbe
1​
1​
Nicklas Persson
1​
0​
Robert Pirés
1​
0​
Roy Präger
1​
0​
Rade Prica
1​
0​
Gilbert Prilasnig
1​
0​
Carsten Ramelow
1​
0​
Paulo Rink
1​
0​
Sávio
1​
0​
Lionel Scaloni
1​
0​
Bernd Schneider
1​
0​
Markus Schupp
1​
0​
Georgios Seitaridis
1​
0​
Serginho
1​
0​
Diego Simeone
1​
0​
Bent Skammelsrud
1​
1​
Santiago Solari
1​
0​
Jan Derek Sorensen
1​
0​
Alessio Tacchinardi
1​
0​
Jeffrey Talan
1​
0​
Michael Tarnat
1​
0​
David Trezeguet
1​
0​
Igor Tudor
1​
0​
Turu Flores
1​
0​
Ronny Venema
1​
0​
Juan Sebastián Verón
1​
0​
Jason Wilcox
1​
0​
Sylvain Wiltord
1​
0​
Dwight Yorke
1​
0​
Souleymane Youla
1​
0​
Zlatko Zahoviè
1​
0​
 

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
164
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
ok, got it

but I need to remove all (value) from column goals, is that ok for you ? or you need these values for something more?

That is fine, thanks Sandy
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,451
here is M-code for this table
you need Close&Load as connection
then from right click in Workbook Queries use Load to... (choose table and place)

Code:
[SIZE=1]// Table 0
let
    Bron = Web.Page(Web.Contents("https://www.voetbal.com/doelpuntenmakers/champions-league-2000-2001/")),
    ROC = Table.SelectColumns(Bron,{"Data"}),
    RBR = Table.RemoveLastN(ROC,1),
    Expand = Table.ExpandTableColumn(RBR, "Data", {"Speler", "Goals (pen)"}, {"Speler", "Goals (pen)"}),
    Split = Table.SplitColumn(Expand, "Goals (pen)", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"Goals (pen).1", "Goals (pen).2"}),
    #"Replaced Value" = Table.ReplaceValue(Split,")","",Replacer.ReplaceText,{"Goals (pen).2"}),
    Type = Table.TransformColumnTypes(#"Replaced Value",{{"Goals (pen).1", Int64.Type}, {"Goals (pen).2", Int64.Type}}),
    Rename = Table.RenameColumns(Type,{{"Goals (pen).1", "Goals"}, {"Goals (pen).2", "Pen"}})
in
    Rename[/SIZE]
 

Watch MrExcel Video

Forum statistics

Threads
1,119,182
Messages
5,576,569
Members
412,733
Latest member
korayuyanik
Top