Hoe een samenvattende lijst maken?

[Dirk299]

Beste,


Ik heb verschillende lijsten met wagens (chassisnummers) die zijn uitgerust met bepaalde opties.
Lijst 1 bevat een lijst van alle wagens uitgerust met optie1.
Lijst 2 bevat een lijst met alle wagens uitgerust met optie2.
Lijst 3 idem voor optie3.
enzovoort.

Er zijn dus wagens die in meerdere lijsten kunnen voorkomen. Het is dus mogelijk dat een wagen één, twee, drie (of meer) opties heeft.

Nu is het de bedoeling dat ik als resultaat bekom:
aantal wagens met één optie, aantal wagens met twee opties, aantal wagens met drie opties, enzovoort.

Hoe pak ik dit best aan?
Heb reeds geprobeerd om onderaan lijst 1 de lijst 2 te plakken en daaronder lijst 3 en dan met een draaitabel te werken, maar ik kom er niet uit.

Iemand tips?

Alvast bedankt,
Dirk

 

[abevleeming]

Voorbeeld bestand (zonder gevoelige informatie) doet wonderen!

Met meteen een vraag erbij: staan auto's dubbel in het hele systeem? Dus komen dezelfde auto's in meerdere lijsten voor?

 

[Dirk299]

Met voorbeeldbestand

Bekijk bijlage Voorbeeldbestand.xlsx

 

[abevleeming]

Ik neem aan dat je meer dan drie opties hebt? Met drie opties is het makkelijk, maar gezien je vraagstelling ga ik ervan uit dat je in je uiteindelijke bestand meer opties hebt?

 

[Dirk299]

Uiteraard gaat het om een veelvoud van opties en chassisnummers. Het voorbeeldbestand is bewust wat korter gehouden omdat ik de 'verwerking/berekening' manueel heb willen uitvoeren.

 

[abevleeming]

Stap 1 is deze:

=SOMPRODUCT(AANTAL.ALS(INDIRECT("'Optie"&RIJ(INDIRECT("1:3"))&"'!A:A");A2))

Met onderstaande code verwijs je naar je tabbladen. Hierbij ben ik nu uitgegaan van opeenvolgende namen van optie1 tot optie3. Als je meer opties hebt kan je "1:3" vervangen voor "1:X" met X je laatste optie. Dit werkt alleen als je met namen als optie1 en optie2 werkt.

"'Optie"&RIJ(INDIRECT("1:3"))

Als je met specifieke namen werkt (airco, cruise control etc..) dan is het handig om een rijtje te maken met de namen van je tabbladen en daar naar te verwijzen door bovenstaande gedeelte van de formule te vervangen voor onderstaande (als in H1:H20 je namen staan):

"'"&$H$1:$H$20

Nu heb je alle waarden, wil je het dan ook nog automatisch gesorteerd hebben? Met excel kan je ook elke keer "handmatig automatisch" sorteren..

 

[Niels28]

Met een kleine macro en een draaitabel

Bekijk bijlage opties.xlsm

Niels

 

[Dirk299]

Beste Abevleming,

dankzij de code lukt het me al om in de juiste richting te gaan.

Klopt het dat de volgende stappen zijn:
1) Alle info van de verschillende lijsten onder elkaar te plakken op het tabblad 'berekenblad'
2) de code =SOMPRODUCT(AANTAL.ALS(INDIRECT("'Optie"&RIJ(INDIRECT("1:3"))&"'!A:A");A2)) in cel C2 te plaatsen
3) de cel C2 doorcopieren tot de laatste rij met chassisnummer
4) de lijst sorteren op chassisnummer
5) de dubbele rijen te verwijderen
6) een draaitabel te maken met als resultaat het aantal wagens met 1, 2 of 3 opties (in dit voorbeeldbestand2 )

wat zou hier nog in kunnen geautomatiseerd worden (ik vermoed met VBA, maar daar heb ik eerlijk gezegd niet al te veel kaas van gegeten.

 

[Niels28]

mijn bijlage nog niet bekeken?

Niels

 

[abevleeming]

Bijlage van Niels is inderdaad een/de mooi(st)e oplossing. Eventueel kan je dan zelfs nog in de draaitabel "optie" aan rijen toevoegen. Dan kan je nog eens zien welke opties het zijn ook. Door "optie" aan kolommen toe te voegen kan je hier nog een op sorteren en heb je een mooi/eenvoudig overzicht. Laat je niet afschrikken door de macro van Niels dat werkt prima zo en ongeacht hoeveel tabbladen je toevoegt, zolang je maar ervoor zorgt dat "berekenblad" en "Resultaat" helemaal rechts staan...

 

[Dirk299]

mijn bijlage nog niet bekeken?

Niels

Bedankt heren.

Sorry Niels, ik had inderdaad je bijlage niet gezien.

Hoe start je de macro op?

Bijkomende vraag: mijn einddoel is te bekomen hoeveel auto's (chassisnummers) zijn uitgerust met 1 enkele optie (en dus niet noodzakelijk is dit optie 1) hoeveel wagens hebben twee opties en hoeveel wagens hebben 3 opties.


dus einresultaat zou kunnen zijn
8 wagens met 1 optie
30 wagens met 2 opties
9 wagens met 3 opties.


Alvast bedankt (alweer).
Dirk

 

[Niels28]

zo is ie nog iets mooier , hoeft rij 2 niet verborgen te zijn

Private Sub Worksheet_Activate()

    With Sheets("berekenblad")
        .Range("A3").Resize(.Cells(Rows.Count, 1).End(xlUp).Row - 2, 2).Delete
        For sh = 1 To Sheets.Count - 2
            sq = Sheets(sh).Range("A2").Resize(Sheets(sh).Cells(Rows.Count, 1).End(xlUp).Row - 1, 2)
            .Cells(2, 1).Resize(UBound(sq), 2).Value = sq
        Next
    End With

End Sub

Niels

 

[Niels28]

macro start automatisch al je het tabblad activeert.

Niels

 

[Niels28]

dan draai je chassisnummer en optie om in de draaitabel.

edit:
sorry las je vraag verkeerd,
plaat daarvoor deze formule op blad resultaat

=AANTAL.ALS(B:B;1)

1 staat voor 1 optie


Niels

 

[Dirk299]

Niels,



Toch gaat er nog iets fout (ik heb je bestand opties hernoemt naar voorbeeldblad3 in bijlage).

Ik vind op het 'berekenblad' bijvoorbeeld de CN1 tot en met 9 niet terug. Op dit berekenblad zouden dus ook 26 chassisnummers moeten gevonden worden met daarachter het cijfer dat overeenkomt met het aantal opties (dus inderdaad voor CN10 = 3, maar voor CN26 = 1 want die heeft enkel de derde optie)

In het bestand zijn in totaal 26 wagens vermeld.

op het resultaatblad zou de som van het aantal wagens dat 1, 2 of 3 opties heft, dus gelijk moeten zijn aan 26.


Dirk

 

[Niels28]

Foutje

toch gebruikt maken van de macro in mijn eerste bijlage.

Niels

 

[Dirk299]

Beste Niels,

We komen stilaan bij de oplossing, maar met de allerlaatste wijzigingen vind ik nu een wagen die zogenaamd 4 opties zou hebben (CN8) terwijl het maximum in dit voorbeeld uiteraard maximaal 3 kan zijn.

Vermoedelijk nog ergens een foutje?
Aangezien de VBA code redelijk onbekend is voor mij, heb ik geen idee waar dit vandaan kan komen.

Dirk
Bekijk bijlage Voorbeeldbestand3.xlsm

 

[Niels28]

Ja er stond nog iets op regel 2, deze heb ik verborgen en moet leeg zijn.
Dit omdat de tabel anders niet aangevuld wordt.

Bekijk bijlage Voorbeeldbestand3 (2).xlsm

Niels

 

[abevleeming]

Omdat de macro rij 2 van berekenblad niet aanpast. Hier stond nog CN8 in... Zo beter?

Bekijk bijlage Auto opties.xlsm

 

[Dirk299]

Heren,


Zonet getest met de echte data en ik kan bevestigen dat het werkt zoals ik het in gedachten had.

Mijn oprechte dank voor jullie hulp.



Dirk
(ik zal gerust kunnen slapen vannacht)