VBA? "Pivottable zonder lege cellen onder kolommen"?

[zipke1988]

Allen,

Moeilijke titel, maar ik weet niet hoe ik het moet omschrijven, vandaar dat ik ook op internet niet veel bruikbaars vind.

Mijn probleem is het volgende. Onderstaand vind je mijn (vereenvoudigde) basistabel:


Onderstaand de pivot table die ik krijg: alle kleuren als aparte kolomtitel. Als ik dan als waarde het "aantal" ingeef, kan ik zien bij welk merk welke kleuren voorkomen.
Ik heb echter een overzichtstabel nodig, waarbij ik achter elke lijn gewoon een opsomming krijg van de mogelijke kleuren (zie tabel "wat ik moet krijgen"). Hoe kan ik dit bekomen? Met geen enkele formule lukt me dit.

Ook "selecteren speciaal>constanten" en dan kopiëren/plakken zodat Excel alle lege cellen zou 'vergeten' lukt niet. Ik kan dit doen per rij, dan lukt het, en per kolom, dan lukt het ook, maar vanaf dat ik kolommen en rijen samen selecteer en probeer te kopiëren, krijg ik de error: "Deze opdracht kan niet worden uitgevoerd op meerdere selecties".



Ik hoop dat mijn voorbeeld duidelijk is.

Is dit iets voor VBA?

Kan iemand mij verderhelpen a.u.b.? Is voor het werk.. alle hulp is welkom!!


Alvast heel erg bedankt!

 

[Gijsbert1]

Zonder gebruik van VBA maar met een hulpkolommetje kwam ik hierop:

Bekijk bijlage Map1_Gijs.xlsx

 

[AlexCEL]

Zonder VBA en hulpkolom hierbij nog een mogelijke oplossing.

Volgende keer graag een EXCEL voorbeeldje plaatsen en geen plaatjes. Nu moest ik overtypen en dat is niet mijn favoriete bezigheid.

 

[zipke1988]

Zonder VBA en hulpkolom hierbij nog een mogelijke oplossing.

Volgende keer graag een EXCEL voorbeeldje plaatsen en geen plaatjes. Nu moest ik overtypen en dat is niet mijn favoriete bezigheid.

Mijn excuses, volledig terecht. Bedankt om alsnog de moeite te doen de oplossing te zoeken!

Mag ik jou vragen hoe de formule is opgebouwd, zodat ik deze volgende keer ook kan gebruiken? Ik begrijp b.v. niet waarvoor de 9E+99 staat? Is dit om de grootst mogelijke bereik te selecteren en is deze 9E hier eigenlijk een wetenschappelijke notatie?

Alvast bedankt

 

[AlexCEL]

Ten eerste: ik heb de formule in het voorbeeld hierboven nog iets versimpeld. Toch vereist het enige excel kennis om de formule te kunnen lezen.
Ten tweede: het betreft matrix-formules, dat wil zeggen dat je ze moet afsluiten met Control+Shift+Enter na invoeren of wijzigen. Matrixformules kunnen overweg met meerdere rijen of kolommen gegevens, een gewone formule veelal niet..

Formules kun je altijd beste van "binnen naar buiten lezen". Een kleine toelichting...

=ALS.FOUT(INDEX($B:$B;KLEINSTE([COLOR="#FF0000"]ALS($A$2:$A$2000=$E3;RIJ($A$2:$A$2000);9E+99)[/COLOR];KOLOM(F3)-KOLOM($E3)));"")

1. Hier wordt per rij (dat maakt het een matrixformule) gezocht in de brontabel (ALS-functie) of het merk (“brand”) overeenkomt met die in de eerste kolom van je overzichtstabel.
2. Als er overeenkomst is wordt het RIJ-nummer genomen, zo niet een heel groot getal (9E+99 = 9 tot de macht 99).
Resultaat voor de eerste zoekterm in de opzoektabel (“a”) is een matrix die die lijkt op: 2, 3, 4, 9E99, 9E99, etc.
3. Via de KLEINSTE-functie wordt de kleinste waarde uit deze matrix gezocht, oftewel het eerste rijnummer waarop overeenkomst is. In dit geval dus 2.
4. Via de INDEX-functie wordt nu in kolom B de 2e rij getoond.

=ALS.FOUT(INDEX($B:$B;KLEINSTE(ALS($A$2:$A$2000=$E3;RIJ($A$2:$A$2000);9E+99);([COLOR="#FF0000"]KOLOM(F3)-KOLOM($E3)[/COLOR]));"")

5. Het rode gedeelte hierboven zorgt ervoor dat je een reeks 1,2,3, etc. krijgt bij doortrekken van de functie. Dit argument gebruikt de KLEINSTE functie om in kolom F de kleinste waarde te vinden, in kolom G de op 1 na kleinste, in kolom H de op 2 na kleinste etc. op te zoeken.
6. Als er niets gevonden wordt levert de INDEX een foutmelding op die met de ALS.FOUT wordt afgevangen. Deze zorgt er dan voor dat de regel leeg blijft.

Hopelijk verduidelijkt dit voldoende...

 

[zipke1988]

...

Vooreerst ontzettend hard bedankt voor de uitleg. Ik had reeds getracht de formule van binnen naar buiten te lezen, maar de wetenschappelijke notatie 9E+99 e.d. bleven voor verwarring zorgen.
Ik begrijp jouw Excel en ben er succesvol in geslaagd zelf een gelijkaardige (zeer eenvoudige) Excel na te bouwen.

Wanneer ik echter mijn eigenlijke Excel bekijk, loopt het verkeerd en ik weet ECHT niet waarom. Ik heb de code al 10 maal hertypt, herlezen, ... maar niets lijkt het probleem op te lossen... Je vindt de Excel hier: Bekijk bijlage online.xlsx

In de linkse tabel zijn de kolommen met "tekst" telkens een kopie van de eerste lijn, gezien deze werden opgevuld na "samenvoegen cellen" ongedaan te maken en te voorkomen dat de laatste lijnen telkens leeg waren.

Het zou echt super zijn, moest je me hier nog even in kunnen helpen. Ik weet dat je bv. bij vert.zoeken moet zorgen dat de zoekwaarden in de eerste kolom staan e.d. maar dat is hier niet vereist denk ik gezien je met de index werkt?


Nogmaals enorm hard bedankt al voor je tijd!

 

[AlexCEL]

Vul in jouw geval in:

S3: =ALS.FOUT(INDEX($E:$E;KLEINSTE(ALS($A$3:$A$10000=$I3;RIJ($A$3:$A$10000);[COLOR="#0000FF"]10^9[/COLOR]);KOLOM(S3)-[COLOR="#FF0000"]KOLOM($R3)[/COLOR]));"")

Blauw: notatie 9E+99 vervangen voor (hier als voorbeeld) 10^9... gaat erom dat hier een voldoende groot getal staat.
Rood: zie opmerking 5 bij de uitleg van de formule in post #5.

En nogmaals: het is een matrix-formule, dus afsluiten met control+shift+enter.

 

[zipke1988]

Vul in jouw geval in:

S3: =ALS.FOUT(INDEX($E:$E;KLEINSTE(ALS($A$3:$A$10000=$I3;RIJ($A$3:$A$10000);9E+99);KOLOM(S3)-[COLOR="#FF0000"]KOLOM($R3)[/COLOR]));"")

Zie opmerking 5 bij de uitleg van de formule in post #5.

En nogmaals: het is een matrix-formule, dus afsluiten met control+shift+enter.

Het werkt!!

Ik had al wat opgezocht over matrix-formules want dit was de eerste keer dat ik ze tegenkwam. Na jouw eerste post had ik reeds gezocht op "accolades in Excel-formule" en kwam ik uit bij matrix-formule.
Nu ik weet dat ik kolom R moet gebruiken, begrijp ik jouw functie. Het was "slecht toeval" dat jouw zoekwaarde in de kolom er net voor stond. Ik ging er dus vanuit dat hier ergens een verband mee was, vandaar dat ik op zoek ging naar mijn zoekwaarde, nl. kolom I.

In principe is het dus: KOLOM(eerst volgende kolom die moet ingevuld worden)-KOLOM($laatste kolom voor eerste in te vullen kolom).

Top! Heer erg bedankt!! Ik weet niet of ik hier credits kan geven, dan moet je maar zeggen hoe, je verdient ze :thumb:

 

[AlexCEL]

In principe is het dus: KOLOM(eerst volgende kolom die moet ingevuld worden)-KOLOM($laatste kolom voor eerste in te vullen kolom).

Inderdaad, dan krijg je bij doortrekken van de formule een reeks 1,2,3,...

Had formule in post #7 ook nog beetje aangepast n.a.v. een opmerking m.b.t. tot de 9E+99. Vervangen door 10^9. Maakt niet zoveel uit wat hier staat, als het maar een voldoende groot getal is.

Rechts bovenaan de pagina is ook een optie "stem op deze vraag" als je credits wilt geven. Zegt niets over het antwoord echter...

En als je meer wilt weten over matrixformules, deze is wel duidelijk vind ik: http://www.h2o-betterwin.nl/know-how/tutorials/excel-matrixformules-basis/