[EXCEL] Lijst met aantallen op basis van types

[vanpeers]

Hallo allen!

Ik zit met een probleem in excel waarvan ik de geschikte oplossing niet kan vinden.

Info:
Ik trek regelmatig een lijst met (is een fictief voorbeeld) met verschillende producten, industrie en een aantal (buiten vele andere nuteloze kolommen) zoals je hieronder of via de link kan zien.

Wat zou ik willen:
Nu wil ik als ik de lijst update (of na een druk op een knop met macro) op een andere sheet/workbook een lijst willen krijgen per "Industrie" met daaronder een lijst met de totalen per product, gesorteerd van groot naar klein.
De "Industrie types" veranderen niet, regelmatig komen er wel andere producten bij die dus ook in de lijst moeten komen.
In de kolom getest zou ik met een countif data laten halen uit een andere workbook om aan te geven hoeveel er getest zijn van het product er naast.

Link excel voorbeeld https://docs.google.com/s...752378&rtpof=true&sd=true


Wat heb ik al geprobeerd:
Allerlei opties gezocht en geprobeerd.
Dichtste bij dat ik kom is met 1 malig een lijst te maken van alle producten met hiernaast een Sumif op basis van industrie en product. Daarne macro laten lopen om alles te sorteren.

Maar hierdoor komen nieuwe producten niet op de lijst en is deze vaak niet correct of up-to-date.

Extra moeilijkheden:
De extra moeilijkheid is dat:
- voor de productnaam staat soms bv T-
- Na de productnaam staat soms -A1
- Soms beide, soms niet

In de tabel zou dit getrimmed moeten worden. Maar met =LEFT of =RIGHT kom ik er niet, ook als ik bv text.before delimiter gebruik bv "-" achteraan dan krijg ik bij namen zonder deze "-" achteraan een lege cel.


Ook in het overzicht zou ik per groep (in dit voorbeeld, vlees, vegan, drank) willen sorteren van groot naar klein op basis van "Getest", bij een pivottable gaat dit niet omdat dit niet bij de pivottable hoort.


Welke functie zou ik hiervoor kunnen gebruiken?

[ Voor 23% gewijzigd door vanpeers op 23-02-2024 14:16 ]

[Nicap]

Google eens op draaitabellen. (Of pivottables)

[vanpeers]

Nicap schreef op donderdag 22 februari 2024 @ 16:05:
Google eens op draaitabellen. (Of pivottables)

Met draaitabellen krijg ik niet alles werkende, mijn voorbeeld was misschien te simplistisch.

Ik pas de openingspost aan.

De extra moeilijkheid is dat:
- voor de productnaam staat soms bv T-
- Na de productnaam staat soms -A1
- Soms beide, soms niet

In de tabel zou dit getrimmed moeten worden. Maar met =LEFT of =RIGHT kom ik er niet, ook als ik bv text.before delimiter gebruik bv "-" achteraan dan krijg ik bij namen zonder deze "-" achteraan een lege cel.


Ook in het overzicht zou ik per groep (in dit voorbeeld, vlees, vegan, drank) willen sorteren van groot naar klein op basis van "Getest", bij een pivottable gaat dit niet omdat dit niet bij de pivottable hoort.

Hieronder ook een aangepast voorbeeld:

[ Voor 36% gewijzigd door vanpeers op 23-02-2024 14:17 ]

[jbdeiman]

Voor het vervangen van de tekst moet je wellicht Substitute gebruiken?

Daarnaast denk ik dat je met 'INDEX" moet gaan werken, zie bijvoorbeeld dit filmpje. Je kan op die manier op basis van bepaalde posities in een tabel bepalen welk item je op wilt halen.

[Nicap]

vanpeers schreef op vrijdag 23 februari 2024 @ 14:14:
[...]


Met draaitabellen krijg ik niet alles werkende, mijn voorbeeld was misschien te simplistisch.

Ik pas de openingspost aan.

De extra moeilijkheid is dat:
- voor de productnaam staat soms bv T-
- Na de productnaam staat soms -A1
- Soms beide, soms niet

In de tabel zou dit getrimmed moeten worden. Maar met =LEFT of =RIGHT kom ik er niet, ook als ik bv text.before delimiter gebruik bv "-" achteraan dan krijg ik bij namen zonder deze "-" achteraan een lege cel.


Ook in het overzicht zou ik per groep (in dit voorbeeld, vlees, vegan, drank) willen sorteren van groot naar klein op basis van "Getest", bij een pivottable gaat dit niet omdat dit niet bij de pivottable hoort.

Hieronder ook een aangepast voorbeeld:
[Afbeelding]

Je zou dat data dan ook in de query editor kunnen laden en daar verwerken om de data op te schonen.
Dan in een tabel zetten voor verder gebruik.

[vanpeers]

jbdeiman schreef op vrijdag 23 februari 2024 @ 14:45:
Voor het vervangen van de tekst moet je wellicht Substitute gebruiken?

Daarnaast denk ik dat je met 'INDEX" moet gaan werken, zie bijvoorbeeld dit filmpje. Je kan op die manier op basis van bepaalde posities in een tabel bepalen welk item je op wilt halen.

Substitue lijkt enkel bedaalde tekens,letters te kunnen vervangen? Het lastige is dat er soms een "-" staat, soms 1 en soms 3, de tekst die ik wil staat nog op dezelfde plaats.

De index functie ken ik nog niet, hoe zou ik dit precies moeten gebruiken?
Als ik even kijkt lijkt het niet mogelijk om op 1 product te zoeken en daar alle totalen van te laten optellen?

Nicap schreef op vrijdag 23 februari 2024 @ 15:01:
[...]


Je zou dat data dan ook in de query editor kunnen laden en daar verwerken om de data op te schonen.
Dan in een tabel zetten voor verder gebruik.

Dit was ik ook aan het bekijken, lijkt wel zéér traag te werken.


Ter info, gaat over een lijst met 10k lijnen tot 100k lijnen, met een kolom of 30
Ik experimenteer alvast verder met jullie input.

[jbdeiman]

vanpeers schreef op vrijdag 23 februari 2024 @ 15:30:
[...]


Substitue lijkt enkel bedaalde tekens,letters te kunnen vervangen? Het lastige is dat er soms een "-" staat, soms 1 en soms 3, de tekst die ik wil staat nog op dezelfde plaats.

De index functie ken ik nog niet, hoe zou ik dit precies moeten gebruiken?
Als ik even kijkt lijkt het niet mogelijk om op 1 product te zoeken en daar alle totalen van te laten optellen?

Ik had niet scherp uit jouw voorbeelden dat het niet specifiek de tekst T- of -A1 was die er stond, maar dat dit ook bijvoorbeeld T-- en ---A1 kan zijn? Of kan er willekeurig wat staan, als in XX----Productnaam---A1A en wil je daar alleen 'Productnaam' uit overhouden?

Ik dacht dat k een linkje erin had staan voor het gebruik van de Index functie, maar bij deze dan
YouTube: Google Sheets INDEX Function: How To and Use Cases

[Nicap]

Power query gebruiken is normaal gesproken niet langzaam hoor. Het is juist bedoeld voor heel veel input.

YouTube: How to use Microsoft Power Query
Bijvoorbeeld

[ Voor 32% gewijzigd door Nicap op 23-02-2024 16:50 ]

[dixet]

Dat opsplitsen van die productnaam gaat prima met TEXTSPLIT op "-".
Dan hou je een array met 1 (als de pruductnaam kaal in de cel stond), twee (als er T- voor of - A achter stond) of 3 elementen (als er zowel T- voor als - A achter stond)
Omdat er zowel wat voor als achter de productnaam kan zitten moet je nog wat slims verzinnen om het juiste deel te pakken, waarschijnlijk werkt dat het beste door te kijken naar de lengte. Is INDEX(1) langer dan 2 tekens? Dan pak je die. Is INDEX(2) langer dan 2 tekens dan neem je die.

Zoiets (zonder excel ingetikt dus tel ff goed de haakjes na)

code:

=LET(ts;TEXTSPLIT(A1;"-");IFS(LEN(INDEX(ts;1;1))>2;INDEX(ts;1;1);LEN(INDEX(ts;2;1))>2;INDEX(ts;2;1);INDEX(ts;2;1)))

[RunBV]

Ik heb een poging gewaagd met power query. Moeilijkheid zit het hem naar mijn mening vooral als er maar 1x een "-" wordt gebruikt, in mijn uitwerking ben ik er van uitgegaan dat het eerste gedeelte de juiste tekst is maar dat hoeft niet natuurlijk (in de voorbeelddata wel).

Ik denk dat dit ook moeilijk met logica goed te zetten is tenzij er vaste regels zijn in de naamsopbouw. Voor de kwaliteit van rapportages zou ik persoonlijk eerder met een vertaaltabel werken en die updaten als er nieuwe producten bijkomen dan er van uitgaan dat de logica altijd de juiste resultaten geven.

Link naar het bestand: https://1drv.ms/x/s!AsnDzeYZnoPYshxmj6oVuvbeuGtM?e=7cP5SA

[ Voor 2% gewijzigd door RunBV op 24-02-2024 13:15 . Reden: Update link ]

[Nicap]

Het is altijd aan te raden om je bron gestructureerd te maken en te houden. Dat voorkomt een hoop fouten en werk.

[vanpeers]

dixet schreef op vrijdag 23 februari 2024 @ 18:05:
Dat opsplitsen van die productnaam gaat prima met TEXTSPLIT op "-".
Dan hou je een array met 1 (als de pruductnaam kaal in de cel stond), twee (als er T- voor of - A achter stond) of 3 elementen (als er zowel T- voor als - A achter stond)
Omdat er zowel wat voor als achter de productnaam kan zitten moet je nog wat slims verzinnen om het juiste deel te pakken, waarschijnlijk werkt dat het beste door te kijken naar de lengte. Is INDEX(1) langer dan 2 tekens? Dan pak je die. Is INDEX(2) langer dan 2 tekens dan neem je die.

Zoiets (zonder excel ingetikt dus tel ff goed de haakjes na)

code:

=LET(ts;TEXTSPLIT(A1;"-");IFS(LEN(INDEX(ts;1;1))>2;INDEX(ts;1;1);LEN(INDEX(ts;2;1))>2;INDEX(ts;2;1);INDEX(ts;2;1)))

RunBV schreef op zaterdag 24 februari 2024 @ 13:14:
Ik heb een poging gewaagd met power query. Moeilijkheid zit het hem naar mijn mening vooral als er maar 1x een "-" wordt gebruikt, in mijn uitwerking ben ik er van uitgegaan dat het eerste gedeelte de juiste tekst is maar dat hoeft niet natuurlijk (in de voorbeelddata wel).

Ik denk dat dit ook moeilijk met logica goed te zetten is tenzij er vaste regels zijn in de naamsopbouw. Voor de kwaliteit van rapportages zou ik persoonlijk eerder met een vertaaltabel werken en die updaten als er nieuwe producten bijkomen dan er van uitgaan dat de logica altijd de juiste resultaten geven.

Link naar het bestand: https://1drv.ms/x/s!AsnDzeYZnoPYshxmj6oVuvbeuGtM?e=7cP5SA

Bovenstaande zijn knappe opties maar ik krijg ze niet (100%) werkende, probleem is vooral dat er absoluut geen structuur zit in de productnamen.
Het gaat vooral mijn excel skills te boven.

Nicap schreef op zaterdag 24 februari 2024 @ 20:06:
Het is altijd aan te raden om je bron gestructureerd te maken en te houden. Dat voorkomt een hoop fouten en werk.

Inderdaad.
Ik heb en workaround gevonden, niet de "mooiste" oplossing maar wel 99% accuraat.

1) Lijst getrokken van alle actieve productnamen (30k )
2) Delen manueel of met TRIM functie ingekort
3) In rapport al deze productnamen per productgroep opgelijst.
4) Daarna weer gewoon laten zoeken met SUMIFS

Nadelen:
- Er kan in de loop van het jaar een nieuwe productnaam bijkomen maar ik krijg nu wel melding van het systeem als er een nieuwe bij komt, hierna moet ik deze manueel bij in het rapport zetten. (enkele per jaar, dus doenbaar)
- Lange lijst met productnamen in het rapport is lang maar op zicht niet zo'n probleem aangezien deze automatisch gesorteerd worden op aantal groot naar klein, degene met "0" kan ik eventueel wel automatisch verbergen


In ieder geval bedankt allemaal voor het meedenken!