Subtotalen stroomlijnen

[Hans Russens]

Hallo allemaal,

Hierbij een uitdaging bij het onderwerp SUBTOTALEN.
Ik heb een overzicht waarbij ik het een aantal orders/aantallen per order in een regio heb.
Behalve het totaalaantal per regio, wil ik ook het aantal orders in de regio weten.

Met 2 keer de functie SUBTOTALEN aanroepen heb ik het resultaat wat ik wil zien.
In het bestand dat ik wil bewerken staan echter veel meer regio's en ziet dat er erg rommelig uit.
Dus wil ik deze twee SUBTOTALEN graag op 1 regel

Om het mooi te maken zou ik ook deze regel helemaal vet en gekleurd (in dit geval blauw) willen maken.

Overigens is het niet mogelijk om met een draaitabel te werken omdat ik deze gegevens nog verder wil bewerken.

Hierbij ook het bestand zoals het is (blad1), ik het verwerk met 2* SUBTOTALEN (Blad2) en zoals ik het graag wil hebben (Blad3).

Alvast heel erg bedankt,

 

[Ad Becude]

Hans,

Je bedoel je zoiets?

 

[Haije]

of met een draaitabel

Hans,

je zou het ook met een draaitabel kunenn doen, zie bijlage

 

[Hans Russens]

Beste Ad en Haije,

Zoals aangegeven is een draaitabel lastiig omdat ik de gegevens nog verder wil bewerken.
De oplossing van Ad is een mogelijkheid.
Ik wil echter het blad met de subtotalen (en details) zoveel mogelijk in stand houden.

Overigens bewerk ik het rapport met VBA vanuit een .txt bestand.
Ik zou dit ook codegestuurd kunnen doen, maar weet (behalve met voorwaardelijke opmaak de kleur bepalen) niet hoe ik AANTAL en TOTALEN op 1 lijn krijg.

Groet,

 

[Haije]

Hans,

je kunt de gegevens uit een draaitabel echt nog wel verder bewerken, hoor.
Wat wil er nog mee doen?

 

[sylvester-ponte]

hallo Haije

Beste Ad en Haije,

, maar weet (behalve met voorwaardelijke opmaak de kleur bepalen) niet hoe ik AANTAL en TOTALEN op 1 lijn krijg.

Groet,

ik begrijp je niet helemaal
in blad 3 staan ze toch op een lijn????

groet sylvester

 

[zapatr]

ik begrijp je niet helemaal
in blad 3 staan ze toch op een lijn????

sylvester:
In blad 2 staat het resultaat zoals het nu is, in blad 3 zoals Hans het WENST (zie zijn eerste bericht).


Hans,
via vba is je probleem zeer goed op te lossen.
Het is wel wat gepuzzel en het zou mij ettelijke uren kosten om het netjes te doen.
Maar op dit forum zijn er waarschijnlijk wel bezoekers die dat sneller kunnen.

 

[sylvester-ponte]

hans

geef eens aan hoe je aan blad2 komt (aanmaakt)

 

[Hans Russens]

Beste Sylvester,

Hierbij de code die ik aanroep om de SUBtotalen aan te maken:

 Columns("A:C").Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True

Beste Haje,

Na bewerken stuur ik de rapportage door aan diverse mensen.
Deze zijn gewend (of verwend) aan de huidige rapportage en gebruiken daar ook weer delen van.
Als ik de basis lay-out verander dan moet ik (of de gebruikers) vervolgstappen ook aanpassen, en dan ben ik behoorlijk wat tijd kwijt.
Op dit moment verander ik handmatig de bestanden voordat ik ze verstuur en heb het idee dat dit in één slag zou moeten kunnen.
Dus bij voorkeur wil ik met de huidige lay-out verder.

Vriendelijke groet,

 

[sylvester-ponte]

zo iets?

Sub totalenInvullen()
  Dim Ra, C As Range
  Dim T As String
  Cells(2, 1).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3), _                               'eerst overals xlSsum in kolom B en C ( 9 staat voor xksum)
      Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  Set Ra = Columns("B:B").SpecialCells(xlCellTypeFormulas)     'dat zijn de cellen in kolom B waar formules staan (nu nog subtotaal met xlsum)
  For Each C In Ra                                  'nu voor iedere formule in kolom B de formule aanpassen en de kleur instellen
  Rows(C.Row).Font.ColorIndex = 41   'kleur instellen
    T = C.Formula     
    T = Replace(T, "(9", "(2")
    C.Formula = T                                   '                           en nu de 9 in een 2 veranderen dan rekent hij de aantallen uit en niet de som
  Next C
  Columns("A:A").EntireColumn.AutoFit                        '    kolom A op breedte instellen
End Sub

groet sylvester

 

[Hans Russens]

GE-WEL-DIG :d:d:d, dit is precies wat ik zocht.

Ik heb nog even moeten puzzelen wat er nu gebeurt in de routine om het pasbaar te maken, maar dat is inmiddels gelukt.

Met de toevoeging:

Rows(C.Row).Font.ColorIndex = 41: Rows(C.Row).Font.Bold = True: Rows(C.Row).NumberFormat = "0"​

maak ik de hele rij blauw, vet, en omdat ik datums (ipv orders zoals in het voorbeeld) optel maak ik de rij nog standaard op.

:thumb: Hardstikke bedankt voor al jullie hulp en meedenken :thumb:.

Groetjes,

 

[zapatr]

Zoals ik al eerder schreef, zou het mij nogal wat tijd kosten om een oplossing via vba in elkaar te knutselen.
Dat heeft het ook, maar nu ik er bijna mee klaar ben, zie ik dat het niet meer nodig is.
(een alternatief zonder gebruikmaking van de functie subtotaal).

 

[Hans Russens]

Beste Zapatr,

Ik sta open voor alle opties, en op mijn leeftijd ben ik zeker bereid om nog wat te leren.
Wellicht is jouw oplossing ook een hele goeie en kan ik deze inzetten.

Daarbij wil ik je niet teleurstellen, zeker omdat je al een hoop energie in de oplossing hebt gestopt.
Ik zou zeggen laat maar komen als je zover bent.

Groet,

 

[zapatr]

Het gebruik maken van ingebouwde functies in Excel (toegepast in de maco van Sylvester)
is beter, omdat dat doorgaans sneller werkt Bovendien is de macro van Sylvester veel korter.
Maar op verzoek van de vragensteller, toch maar deze macro gepost.
Ga naar blad2 en gebruik sneltoets Ctrl-q .

 

[Hans Russens]

Beste Zapatr,

Briljant :thumb:, geweldig om te zien dat je op verschillende manieren aan hetzelfde resultaat kunt komen.

Ik moet eerlijk toegeven dat ik redelijk wat ervaring heb met VBA, maar begin meestal met het opnemen van een Macro die ik dan verder uitwerk met VBA.
Prachtig om dan "het echte werk" te zien zoals jouw creatie.
Alhoewel ik redelijk wat kan maken voel ik me dan toch een "beginnertje"

Zoals je zelf al aangeeft is de code van Sylvester wat universeler toe te passen, maar moet zeggen dat het bestuderen van jouw code erg leerzaam is (wellicht ook voor toekomstige projecten).

Nogmaals: goed om meerdere opties te kennen.

Hartelijk dank voor jullie hulp, en nog veel plezier met de hobby :d :d

Groeten,

 

[zapatr]

Zoals je waarschijnlijk opgemerkt hebt, is het niet nodig dat op blad1 de orders per regio bij elkaar staan.
De macro plaatst ze automatisch bij elkaar, ook als ze op blad1 door elkaar staan.
Zelf zou ik op blad2 na de blauw gekleurde rijen een lege regel inlassen, maar dat mag misschien niet omdat de bestaande opzet gehandhaafd moet worden??. Mocht je die lege rij toch willen, voeg dan in de macro juist boven de achtste regel van onder (dus juist boven "Next x") in: z = z + 1