Excel oplossing

[Sderooij94]

Mijn vraag is de volgende:

Ik heb 150 excel worksheets (150 weken) met ieder een top 200 van het aantal videoweergaven van Hollywood films met daarin de volgende kolomgegevens:
-Datum (150 verschillende datums, per worksheet 1)
-Naam (Naam van de film)
-Weergaven (aantal weergaven in die week)

Mijn doel is om een compleet overzicht te krijgen met het totaal aantal weergaven van elk van de verschillende films beginnend vanaf week #1 t/m week #150. En dan moet bijv film "ABC"als deze in periode 1 t/m 13 blijft voorkomen iedere week opgeteld worden bij de vorige week.

Hoe pak ik dit het beste aan? Want van datumpunt 1 naar datumpunt 2 komen er bijv. 176 v/d 200 films overeen en zijn er dus alweer 24 nieuwe die misschien in datumpunt 3 alweer verdwenen zijn uit de top 200.

Ik hoor graag jullie suggesties!

[HenkEisjedies]

Waarom heb je 150 sheets en niet gewoon 1? Kan ook prima in meerdere sheets, maar het lijkt me makkelijker om gewoon 1 sheet te hebben met:
Datum
Naam Film
Weergaven

Daarna kun je gewoon met een if-statement een telling uitvoeren.

[Raznov]

vba code schrijven om alle worksheets samen te voegen in 1, en daarna een som op een if statement of een pivot table gebruiken.

[Sderooij94]

HenkEisjedies schreef op woensdag 2 oktober 2019 @ 13:33:
Waarom heb je 150 sheets en niet gewoon 1? Kan ook prima in meerdere sheets, maar het lijkt me makkelijker om gewoon 1 sheet te hebben met:
Datum
Naam Film
Weergaven

Daarna kun je gewoon met een if-statement een telling uitvoeren.

De 150 sheets zijn ieder afzonderlijk gedownload. Dus eerst moet ik deze samen voegen. Dat zijn dan dus 30.000 regels in 1 sheet waaruit ik dan per week de verschillende films wil optellen.

[Sderooij94]

Raznov schreef op woensdag 2 oktober 2019 @ 13:57:
vba code schrijven om alle worksheets samen te voegen in 1, en daarna een som op een if statement of een pivot table gebruiken.

Nadat ik ze heb samengevoegd. Is het dan het handigste als ik eerst een kolom maak met daarin alle unieke filmnamen (dit zijn er dus minimaal 200 maar waarschijnlijk een paar 1000)? Dan kan ik deze in kolom A zetten, en nummer ik B1, C1, D1 etc. tot 150 weken.

En dan gebruik ik in de cellen jouw tactiek om filmnaam A + week X te combineren? Ik moet namelijk hoe dan ook controleren of een film in week X weergaven heeft gehad en dit dan optellen bij de som van alle voorafgaande weken voor die specifieke film.

[Patrick_6369]

Als de sheets allemaal dezelfde opmaak hebben (dus allemaal dezelfde kolommen) kun je ze met de PowerQuery add-inn van Excel importeren en samenvoegen en vervolgens met PowerPivot groeperen. Of je gebruikt zelfs PowerBI voor een mooi dashboard.

Als je die tools nog niet gebruikt hebt zou ik wel een aantal tutorials bekijken hoe het werkt voordat je aan de slag gaat maar dit is echt de meest duurzame oplossing. Want als straks sheet nummer 151 komt kun je dat bestand gewoon in dezelfde map opslaan, de query refreshen en het totaaloverzicht wordt bijgewerkt. Heb je dus geen werk meer aan.

[HenkEisjedies]

Ik heb dit net even in elkaar gedraait:
A B C
1 Film Datum Weergaven
2 Jaws 1-10-2019 50
3 Jaws 2-10-2019 40
4 Jaws 3-10-2019 30
5 Pulp Fiction 1-10-2019 100
6 Pulp Fiction 2-10-2019 50
7 Pulp Fiction 3-10-2019 150

Met Gegevensvalidatie heb ik alle verschillende waarden in kolom A in een dropdown gezet in F1. Dit hoeft niet, je kan ook gewoon in die cel de filmnaam zelf typen, maar dit is iets meer user-friendly.

Daarna heb ik in de cel ernaast een SUMIF gemaakt =SUMIF(A2:A7; F1;C2:C7)
Die kijkt naar alle waarden tussen A2 en A7, vergelijkt die met de filmnaam in F1, als hij dan een match heeft dan telt ie de waarden uit kolom C bij elkaar op.

Let me know als het gelukt is.

Patrick_6369 schreef op woensdag 2 oktober 2019 @ 15:05:
Heb je dus geen werk meer aan.

Zo'n oplossing zou ik ook voor kiezen inderdaad, maar in TS zijn geval zou ik het wat handmatiger houden. Is waarschijnlijk sneller ook.

[Sderooij94]

Patrick_6369 schreef op woensdag 2 oktober 2019 @ 15:05:
Als de sheets allemaal dezelfde opmaak hebben (dus allemaal dezelfde kolommen) kun je ze met de PowerQuery add-inn van Excel importeren en samenvoegen en vervolgens met PowerPivot groeperen. Of je gebruikt zelfs PowerBI voor een mooi dashboard.

Als je die tools nog niet gebruikt hebt zou ik wel een aantal tutorials bekijken hoe het werkt voordat je aan de slag gaat maar dit is echt de meest duurzame oplossing. Want als straks sheet nummer 151 komt kun je dat bestand gewoon in dezelfde map opslaan, de query refreshen en het totaaloverzicht wordt bijgewerkt. Heb je dus geen werk meer aan.

Klinkt als een goede tactiek. Echter heb ik het probleem dat de sheets in een CSV file staan. Werkt de PowerQuery add-inn dan ook of moet ik eerst alle 150 sheets omzetten naar kolommen en kan ik dan pas verder?

[Patrick_6369]

Geen probleem hoor, Powerquery kan ook met csv of txt werken, zolang de bestanden maar telkens exact dezelfde indeling hebben.

[michielRB]

Dit schreeuwt om iets robuuster. Bijvoorbeeld je excelsheets in een (mysql) database te importeren en van daaruit je queries te doen.

online tool dat zou kunnen helpen: https://sqlizer.io/#/

[Voor 18% gewijzigd door michielRB op 02-10-2019 15:43]

[HenkEisjedies]

Als alle files CSVs zijn is het nog makkelijker:
http://merge-csv.com/
Of iets nerderiger via je command prompt:
https://www.rondebruin.nl/win/s3/win021.htm

michielRB schreef op woensdag 2 oktober 2019 @ 15:37:
Dit schreeuwt om iets robuuster. Bijvoorbeeld je excelsheets in een (mysql) database te importeren en van daaruit je queries te doen.

online tool dat zou kunnen helpen: https://sqlizer.io/#/

Nee, dit schreeuwt om een oplossing die TS zijn probleem oplost. Wij tweakers hebben vaak de neiging alles te willen automatiseren, maar in de meest extreme gevallen volstaat een stuk papier en een pen of een simpele spreadsheet. Hoeveel uur heb jij gestoken om SQL onder de knie te krijgen totdat je inner joins kon doen?