Excell sorteer vraagje

[joker55]

Ik heb een lijst in Excell gemaakt (soort klanten- en fakturenbestand), en in een bepaalde rij is aangegeven of eea een abonnement is, of een losse verkoop. 

Nu wil ik in twee andere tabblad rijen maken, de ene voor abonnementen, de andere tabblad de losse verkopen. 

Dat geef ik aan met de formule in het tabblad abonnement:  =ALS(klanten!E4=abonnement;klanten!A4;""

En in het tabblad losse verkoop: =ALS(klanten!E4=losse;klanten!A4;""  

 

Dit gaat redelijk......alleen er vallen dan gaten in de rij (abonnement) als die een losse verkoop betreft, en andersom.

Hoe kan ik de twee aparte rijen nu netjes aan laten sluiten?

 

voorbeeld

klant 1    abonnement.

klant 2    abonnement

klant 3    losse verkoop

klant 4    abonnement

klant 5    losse verkoop

klant 6    losse verkoop

 

de twee aparte rijen worden dan in het abonnement tabblad::

 

klant 1    abonnement.

klant 2    abonnement

s p a t i e

klant 4    abonnement

s p a t i e

s p a t i e

enz

 

en in het tapblad voor losse verkoop

 

s p a t i e

s p a t i e

klant 3    losse verkoop

s p a t i e

klant 5    losse verkoop

klant 6    losse verkoop

enz

 

 

Hoe voorkom ik de s p a t i e  in de aparte kolommen?

2 juni 2013 aangepast door joker55

[De_Perre]

Heb hier nu niet direct een NL  versie beschikbaar van Office, maar normaal kan je dit doen via Sort & Filter (knop in excel -> 'sort en filter.png').

 

Indien je tabellen steeds een kolomtitel hebben, kies je deze titel.

Dan kies je de knop sort en filter, en vervolgens filter (filter.png).

 

In de kolomtitel staat er nu een driehoekje om aan te duiden dat je keuzes kan maken... (tabel_1.png)

 

Klik op dit driehoekje (tabel_2.png), en vink de blanks uit. (tabel_3.png)

 

Je zal nu zien dat jouw rijen gefilterd zijn (rijnummers staan in het blauw) en dat er hier en daar in de rijnummers gaten zijn. (na_toepassen_filter.png)

 

Je kan deze lijst dan kopiëren en plakken in een nieuw werkblad, zonder de blanco's.

[De_Perre]

nog 2 opmerkingen:

 

1. In plaats van enkel de kolomtitel te kiezen, zou het kunnen dat je toch de volledige kolommen moet selecteren vooraleer je de filter zet

2. Eigenlijk zou je met de filter op de originele dataset, hetzelfde kunnen doen. Filter plaatsen op beide kolommen; in kolom 2 kies je dan welke keuze je wil zien. Dan de selectie maken en de data naar een nieuw werkblad kopiëren. Zo dien je het hele formule gedoe niet toe te passen

[joker55]

Tsja, ik krijg het zoals voorgesteld niet voor elkaar.

Het gaat erom dat ik eigenlijk niets wil zien, maar gewoon dat eea automatisch gebeurd.

Zodra ik een nieuwe invoer, deze automatisch in het juiste tabblad terecht komt.

Dus niet elke keer opnieuw een functie sorteer moet oproepen. Het moet automatisch gaan, zonder omkijken ernaar.

 

Om de zoveel tijd voer ik een nieuwe naam in, en dan moet deze gewoon terecht komen in het juiste tabblad.

 

6 juni 2013 aangepast door joker55

[De_Perre]

tja, iets minder evident; maar met een draaitabel (pivot table) is het misschien nog te doen.

 

In de ribbon kies je 'Invoegen' en dan het eerste icoon links (zie 'tabel_pivot button.png' en 'pivot button.png')

 

Vervolgens geef je de nodige paramters in (zie 'create_pivot_scherm 1.png') Maak je range voor de selectie groot genoeg.

 

Dan krijg je normaal in de correcte sheet een scherm zoals 'create_pivot_scherm 2.png'.

Hier moet je dan met de zaken aan de rechterkant de opbouw van de draaitabel gaan doen. Je kan de elementen van rechtsboven naar rechtsonder slepen, zodat je scherm 'create_pivot_scherm 3.png' bekomt.

 

Dan moet je gaan filteren. Je bent in het blad abonnement bezig, dus deze wil je hier zien. Klik op de drop-down aangeduid met de pijl en 1) en vink vervolgens selecteer meerdere items (pijl 2). Dan pas kan je de keuze maken op basis van de waarde (pijl 3). ('create_pivot_scherm 4.png' en als resultaat zou je dan 'create_pivot_scherm 5.png' moeten hebben). Sluit de keuze met OK.

 

Vervolgens is jouw pivot gereed en zijn de resultaten gefilterd + gesorteerd! (zie 'resultaat abo.png').

 

Hierna dien je hetzelfde te doen, maar dan voor de losse verkoop. Dus zelfde selectie maken, maar output in het blad losse verkoop zetten. En filter correct instellen.

 

Telkens je een nieuwe klant aan de mastertabel toevoegt, moet je wel de pivots refreshen. Dit kan eenvoudig door een cel in de output te kiezen en vervolgens rechtermuisklik en refresh.

 

Pivots zijn hier niet echt voor gemaakt; en mocht dit je niet verder helpen vrees ik dat je met excel niet echt verder zal geraken. Dan moet je al meer in de richting van een database'je gaan denken...

 

 

[Con]

Als je dit automatisch wil laten

gebeuren moet je toch echt aan de macro.

 

Dan kan alles vanzelf gaan en worden ingevuld.

[De_Perre]

de enige macro die voorzien dient te worden zou eventueel voor de refresh van beide draaitabellen zijn.

Dit zou hij gewoon kunnen opnemen en in een knop in de eerste werkblad kunnen steken in de header row...

[joker55]

Tsja, een macro. Zoiets zit ik ook al te proberen, maar daar ben ik eigenlijk helemaal geen held in.

Of iets met visual basic.....maar ja.

 

Ik probeer nu iets om mbv een tellertje te werken, maar zo lukt het mij niet.

Zal ws toch in visual basic, maar ik ben daar niet bekend in.

 

dus ik moet eens verder kijken.

[Con]

Ik zal eens gaan puzzelen . . .

[De_Perre]

Verversen van de abonnementen

 

Sub refresh_abonnement()
'
' refresh_abonnement Macro
'

'
    Sheets("abonnement").Select
    ActiveCell.Offset(7, 0).Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("klanten").Select
End Sub

 

 

Verversen van de losse verkoop

 

Sub refresh_losseverkoop()
'
' refresh_losseverkoop Macro
'

'
    Sheets("losse verkoop").Select
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "naam[All]", xlLabelOnly + _
        xlFirstRow, True
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    Sheets("klanten").Select
End Sub

 

 

om een knop in de eerste sheet te zetten is het wel nodig om de developer toolbar aan te zetten

via options > customise ribbon > developer

 

ik zal de werboek die ik gemaakt heb als xlsm opslaan, mocht je hem nodig hebben hoor ik het wel.

 

 

*edits*

code werd niet correct weergegeven

11 juni 2013 aangepast door De_Perre