Automatisch sorteren in Excel

[Vissekop]

Hallo,

Ik importeer regelmatig (automatisch) overzichten in Excel, bestaande uit 4 kolommen:

Kolom 1 - Productnaam
Kolom 2 - Target
Kolom 4 - Behaald
Kolom 5 - Verschil (voor of achter op target=kolom 4-3)

Deze kolommen zijn gesorteerd op kolom 1: Productnaam.

In presentaties en rapportages laat ik vaak alleen de top 5 voor / achter op target zien. Hiervoor moet ik de 4 kolommen (handmatig) sorteren op 'Verschil', dan diegene pakken met het grootste / kleinste verschil.

Aangezien ik hier altijd een handmatige sorteerhandeling moet doen, kan ik nooit automatische rapporten genereren.

Is er iemand die weet hoe ik (in 4 nieuwe kolommen naast de 'input' kolommen) automatisch de gesorteerde gegevens kan plaatsen? Evt. via een VBA script?

alvast bedankt,

Vissekop.

Ps. voorbeeldje is bijgevoegd.

 

[Wigi]

Vissekop

1. Ik heb op dit forum al meerdere malen een automatische sortering via formules gedaan. Telkens hetzelfde principe. De RANG functie en de KLEINSTE functie worden gebruikt.

Zie dus in eerdere posts van mij (binnen de laatste 2 maanden) en dan vooral voetbal gerelateerd.

2. Alternatief is de sortering in VBA te programmeren. Ben je daar wat goed in?

Wigi

 

[Vissekop]

Bedankt!

Ik ga zoeken,

In VBA ben ik goed genoeg, maar ik wil e.e.a. zo helder en overzichtelijk mogelijk houden (er werken nogal wat mensen om me heen die geen VBA kennis hebben...), dus gebruik ik bij voorkeur recht-toe-rechtaan functies...

Gr. Remko.

 

[Wigi]

In VBA ben ik goed genoeg,

Dan stel ik voor dat je de VBA code schrijft. Enig probleem is dat de gebruiker VBA code kan afzetten.

Wigi

 

[Wigi]

Anyway,

hier is de code:

Sub Sortering()
    Dim rngNotSorted As Range, rngSorted As Range, rngTemporary As Range
    
    Set rngNotSorted = Range("B7:E16")
    Set rngSorted = Range("G7:J16")
    Set rngTemporary = Range("L7:O16")
    
    rngNotSorted.Copy rngTemporary
    With rngTemporary
        .Sort Key1:=rngTemporary(1, 4), Order1:=xlAscending, Key2:=rngTemporary(1, 1), _
            Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False
        .Cut rngSorted
    End With
End Sub

Wigi

 

[Vissekop]

Een stukje verder

Je voetbal-files kan ik zo snel niet vinden (ik kan niet uitgebreid zoeken i.v.m. teveel gebruikers op de site?)

Ik heb via de 'Small' (ik heb engelse Excel) de 'verschillen' in de goede volgorde gekregen (kolom 5 in mijn file) alleen... hoe krijg ik de rest erbij (de producten, target en verkopen..). Ik heb even vlookup overwogen op de verschillen, alleen gaat dat fout als 2 producten hetzelfde verschil hebben..

Hoe heb je dat opgelost?

Ik wil graag nog ff. verder puzzelen (voor de sport), maar denk inderdaad dat Macro';s een snellere oplossing volgen..

GRoeten en bedankt voor je snelle reacties!

 

[Wigi]

Ik heb via de 'Small' (ik heb engelse Excel) de 'verschillen' in de goede volgorde gekregen (kolom 5 in mijn file) alleen... hoe krijg ik de rest erbij (de producten, target en verkopen..). Ik heb even vlookup overwogen op de verschillen, alleen gaat dat fout als 2 producten hetzelfde verschil hebben..

Hoe heb je dat opgelost?

Ah, nog iemand die voor de sport wat blijft nadenken... :thumb:

Je werkwijze is helemaal juist. De truc is eigenlijk om voor elk product een coëfficiënt te berekenen, afhankelijk van de criteria waarop je sorteert. Hoe lager, hoe beter (bijvoorbeeld, het omgekeerde gaat ook).

Stap-voor-stap uitleg (met Engelse functies):

Bv. coëfficiënt voor product 1:

zet in F7

=RANK(E7;$E$7:$E$16)

Bij gelijke waarden geeft dit problemen. Daarom tel je er nog iets UNIEK bij op:

=RANK(E7;$E$7:$E$16)+ROW(A1)

Maar probleem daar is dat ROW van dezelfde orde van grootte is als RANK. Het sorteren met behulp van ROW (indien meerdere producten dezelfde RANK hebben, is ondergeschikt aan de sortering op basis van RANK). Daarom deel je ROW(A1) door 10 als je minder dan 10 producten hebt, of deel door 100 als het tussen 10 en 100 zijn, deel door 1000 enz.

Dus

=RANK(E7;$E$7:$E$16)+ROW(A1)/100

Trek de formule naar beneden.

Dan gebruik je SMALL voor de gesorteerde kolom met Diff, en tevens gebruik je INDEX functie voor Model, Budget en Target.

Lukt het?

Wigi

 

[Vissekop]

Snappem!

Erg slim

Je voegt een extra waarde toe die 'achter de comma' blijft (als je met hele getallen werkt) en dus de resultaten niet beinvloed, maar wel voor een verschil zorgt zodat je vlookup werkt... Ga het proberen, gaat zeker lukken.

Dit helpt me ontzettend veel!

Ook bedankt voor je VBA code, ziet er eveneens hanteerbaar uit!

Leuk iemand tegen te komen die ook graag puzzelt in Excel (of die gedwongen is veel te puzzelen in excel

Groeten.

 

[Wigi]

Leuk iemand tegen te komen die ook graag puzzelt in Excel (of die gedwongen is veel te puzzelen in excel

Niet gedwongen. Pure interesse en anderen mijn kennis doorgeven.

Veel succes, en vooral, plezier, ermee. Je hebt hem inderdaad gesnapt.

Wigi