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

gravanoc · Oct 7, 2019

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/Column	A	B	C	D	E
1	Player	99-00	00-01	01-02	...
2	Raul	0	7	0	0
3	David Beckham
1822	Raul	0	0	3	0
2455	Raul	0	0	0	12

Logit · Oct 7, 2019

.
If I understand correctly, you are using 228 columns to track all the years/months of goals ?

sandy666 · Oct 7, 2019

with Power Query

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

gravanoc · Oct 7, 2019

Logit said:
.
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 · Oct 7, 2019

sandy666 said:
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 · Oct 7, 2019

without source I can do nothing

sandy666 · Oct 7, 2019

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 · Oct 7, 2019

here is table (217 rows) but there is no duplicates

Speler	Goals	Pen
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 · Oct 7, 2019

sandy666 said:
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 · Oct 7, 2019

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]

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

gravanoc

Active Member

Excel Facts

Logit

Well-known Member

sandy666

Banned - Rules violations

gravanoc

Active Member

gravanoc

Active Member

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

gravanoc

Active Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

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

Active Member

Excel Facts

Well-known Member

Banned - Rules violations

Active Member

Active Member

Banned - Rules violations

Banned - Rules violations

Banned - Rules violations

Active Member

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock