Sorteren opv voorwaarde

[Edtwiest1]

Goedemiddag,

ik heb door het forum gebladerd, maar volgens mij is deze vraag nog niet gesteld (kan het niet vinden in elk geval)

Ik zit met hetvolgende; in bijgevoegd excelbestand heb ik een paar artikelen die meerdere eenheden bevat; de gegevens trek ik weer uit een ander pakket, waar geen sorteermogelijkheden aanwezig zijn (dus moet ik het met deze dump doen)

Zoals je ziet heeft een artikel (item) 4 verschillende eenheden, maar een ervan is de standaardeenheid.

Nu wil ik graag de data dusdanig gesorteerd hebben, dat ik de volgende uitkomst krijg;

Kolom A - Artikelnummer
Kolom B - Artikelomschrijving
Kolom C - de eenheid waarvan de waarde Standaard "Ja" is
kolom D - de standaard waarbij standaard "Ja" is
Kolom E - het aantal waarvan standaard "Ja" is

Vervolgens gesorteerd op de overige eenheden (of alfabet gesorteerd), gevolgd door de bijbehorende standaardwaarde en aantal.

Voor de gewenste uitkomst zie ook het excelbestand.

Hoop dat iemand me kan helpen! Alvast bedankt in elk geval voor het meedenken / lezen!

 

[MarcelBeug]

Bekijk bijlage sorteren voor gevorderden MB.xlsx
Het is niet zo eenvoudig, maar ik heb een oplossing in de bijlage.
In de uitkomstmatrix krijg je eerst het standaardartikel en vervolgens de overige artikelen in de oorspronkelijke volgorde.
Bijvoorbeeld als artikel 2 de standaard is, dan is de volgorde 2, 1, 3, 4, 5. Ander voorbeeld: als 3 de standaard is: 3, 1, 2, 4, 5.

De oplossing werkt met een sorteercode die is gebaseerd op kolomnummers: als in de overeenkomstige regel "Ja" staat in dezelfde kolom, of in de kolom ervoor of in de kolom erna, dan is de sorteercode het kolomnummer, anders 100000 * het kolomnummer.
Voor de sorteercode heb ik de naam Sorteercode gedefinieerd (via tab FORMULES - Namen beheren), waarbij nog van belang is dat ik eerst cel C15 heb geselecteerd (dit is noodzakelijk voor de juiste werking!), voordat ik de naam heb gedefinieerd met formule:

=ALS((Blad1!$B2:$P2="Ja")+(Blad1!$C2:$Q2="Ja")+(Blad1!$D2:$R2="Ja");KOLOM(Blad1!$C2:$Q2);100000*KOLOM(Blad1!$C2:$Q2))

Aangezien de relevante gegevens in kolom 3 t/m 17 staan, levert deze formule de getallen 300000 t/m 1700000 op, behalve voor de 3 kolommen met het standaardartikel: daar komen gewoon de kolomnummers zonder vermenigvuldiging.

Vervolgens in cel C15 de volgende formule:

=INDEX($A2:$Q2;KLEINSTE(Sorteercode;KOLOMMEN($C15:C15))/ALS(KOLOMMEN($C15:C15)<=3;1;100000))

doorgetrokken naar rechts en naar beneden tot en met het einde van de matrix (Q19).
Van links naar rechts levert dit op van regel 2 de kolom conform de kleinste waarde in Sorteercode, de op 1 na kleinste, de op 2 na kleinste en - vanaf de vierde - de op 3 na kleinste gedeeld door 100000, enzovoorts.

 

[Edtwiest1]

Jeetje... ik zat zelf ook al te denken met de index-vergelijken functie, maar dit ziet er goed uit!!!

Het lukt alleen dan nog niet om alle niet-standaardwaardes te sorteren op standaard-eenheid-aantal ???

Maar vooralsnog ben ik een heel stuk verder!!

Heel erg bedankt

ik laat het nog even open staan; misschien dat iemand nog idee heeft om dat ook nog voor elkaar te krijgen, dan ben ik helemaal geholpen!

 

[MarcelBeug]

Volgens mij moet die extra wens met deze opzet ook wel te realiseren zijn: dan doen we niet alles x100000, maar bijvoorbeeld x100000, x200000, x300000 en x400000 (of optellen bij de kolom i.p.v. vermenigvuldigen, dan kan het met REST weer teruggerekend worden), afhankelijk van de sortering.
Maar daar moet ik nog even verder op puzzelen, later vandaag of anders morgen.

En wie weet komt er in de tussentijd nog iemand met een veel betere (VBA?)-oplossing...

 

[MarcelBeug]

Bekijk bijlage sorteren voor gevorderden MB2.xlsx

Het is weer gelukt, maar toch heel anders geworden.
Eenvoudiger wordt het er niet op: ik denk dat met VBA een fraaiere oplossing mogelijk is.
Ik heb nu gewerkt met verschillende formules voor de standaard en niet-standaard eenheden.

Standaardeenheid
Ik heb een naam Standaard gedefinieerd vanuit cel C15, die aangeeft welk artikel de standaard is (1, 2, 3, 4 of 5):

=(Blad1!$D2="Ja")+(2*(Blad1!$G2="Ja"))+(3*(Blad1!$J2="Ja"))+(4*(Blad1!$M2="Ja"))+(5*(Blad1!$P2="Ja"))

Formule in C15, naar rechts gekopieerd t/m E15 en dan naar beneden:

=INDEX($C2:$Q2;Standaard*3-2+REST(2+KOLOMMEN($C:C);3))

Niet-standaard eenheden

Hiervoor heb ik 2 namen gedefinieerd, ten eerste EenhSortCode vanuit C15:

=REST(GROOTSTE((KOLOM(Blad1!$A2:$Q2)+100000*AANTAL.ALS(Blad1!$A2:$Q2;"<="&Blad1!$A2:$Q2))*(0=REST(KOLOM(Blad1!$A2:$Q2);3));{5;4;3;2;1});100000)/3

Deze levert de getallen 1 t/m 5 op in de volgorde van de eenheden, zonder rekening te houden met de standaard (dat komt later).
Bijvoorbeeld het eerste item: 1 (Bundel), 3 (Meter), 2 (Pak), 4 (Pallet), 5 (Stuk/Stuks).

Ten tweede: naam NietStandaardSortCode gedefinieerd vanuit F15:

=INDEX(EenhSortCode;KLEINSTE(ALS(EenhSortCode<>Standaard;{1;2;3;4;5});INTEGER((2+KOLOMMEN(Blad1!$F:F))/3)))

Dit levert in de kolommen F t/m Q op 3x de eerste EenhSortCode, 3x de tweede EenhSortcode etcetera, maar dan zonder de Standaardcode.
Dus voor item 2 krijg je achtereenvolgens 1, 1, 1, 3, 3, 3, 2, 2, 2, 4, 4, 4

Formule in F15, naar rechts gekopieerd t/m Q15 en dan naar beneden:

=INDEX($C2:$Q2;NietStandaardSortCode*3-2+REST(2+KOLOMMEN($F:F);3))

Nog een tip: als je wilt zien wat de formules van de gedefinieerde namen doen, dan kun je de formule (zonder de =) kopiëren in de betreffende cel (C15 of F15) en er =SOM(...) omheen zetten, bevestigen met Ctrl+Shift+Enter en dan evalueren (tab FORMULES - Formule evalueren). Het resultaat van de gedefinieerde naam is dan het stukje juist voordat SOM er op los gelaten wordt.

 

[snb]

Mij lijken de kolommen standaard? in het eindresultaat overbodig.

Sub M_snb()
   sn = Cells(1).CurrentRegion
   
   For j = 2 To UBound(sn)
     sq = Split(Replace(Join(Application.Index(sn, j), "|"), "|Nee|", "_"), "|")
     c00 = Join(Filter(Filter(sq, "_", 0), "Ja", 0), "_")
     
     With CreateObject("System.Collections.ArrayList")
        For Each it In Filter(sq, "_")
           .Add it
        Next
        .Sort
        st = Split(c00 & "_" & Join(.toarray, "_"), "_")
     End With        

     Cells(24 + j, 1).Resize(, 12) = st
  Next
  
End Sub

 

[Edtwiest1]

Heren,

bedankt voor alle hulp!!


De truc van MarcelBeug werkte prima en ik ga zeker ook kijken naar de code van SNB!!

ik sluit dit item in elk geval zo dadelijk!