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

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
165
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Logit

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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
with Power Query

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

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
165
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
165
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
165
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

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
Top