Unieke orders per klant filteren met formule

[Dharwin]

Ik ben op zoek een formule voor in cel D3. Als het klantnummer wordt ingevoerd in het groene vak (C3), moet D3 aangeven hoeveel verschillende, ofwel unieke, orders er door bijv. Klant 1, 2 of 3 zijn geplaatst.

Ik heb uren geprobeerd de goede formule te krijgen met onder andere =interval, maar hiermee kom ik niet verder dan dat deze formule de gehele database optelt. Ik heb dit proberen te combineren met allerlei andere functies, als =aantal.als maar deze formules krijg ik niet werkend.
N.B. in de echte database is er geen mogelijkheid om de orderregels oplopend of aflopend te selecteren. Anders was ik er zelf uitgekomen, door een extra kolom toe te voegen en een vergelijking te maken tussen bijv. regel 1 en 2 en als de orderregels niet gelijk zijn als uitkomst 1 te geven. Hierna had ik dan de functie aantal.als toe kunnen passen, maar in de database staan de ordernummers niet op volgorde.

Ik heb een vereenvoudigd bestand bijgevoegd, waarop de eerste alinea op slaat. zie hier Bekijk bijlage Proefbestand.xlsx

Ik word gestoord dat ik het niet voor elkaar krijg. Wie kan mij verlossen van dit frustrerende vraagstuk?

 

[ExcelAmateur]

Dharwin,

Met het filter is het ook te doen.
Zet in D7 de volgende formule.

=SUBTOTAAL(2;B9:B100)

Of is dit geen optie?

 

[Dharwin]

Bedankt voor de snelle reactie.

Met het filter werken is niet echt een optie, het antwoord zou er uit moeten komen door enkel 1 formule in te vullen. Daarbij haalt de functie subtotaal ook niet de unieke ordernummers op.

Met een draaitabel kan ik trouwens wel achterhalen hoeveel unieke ordernummers er zijn per klant, maar dit is helaas ook geen optie.

 

[ExcelAmateur]

Het kan ook zo.
Er zit een macro in.
Dan zal dit ook niet aan je wensen voldoen.

 

[Dharwin]

Het probleem is niet om het aantal orders te krijgen voor 1 klant, maar het unieke aantal orders.

De uitkomst 7 is dan wel het aantal orderregels, maar niet de aantal unieke orders. Dit zou 2 moeten zijn (namelijk 112 en 115)

 

[ExcelAmateur]

Ik denk dat het nu nog niet goed is.
Misschien dat er nog iemand is die een betere oplossing weet.

Misschien lukt dit met een Matrixformule maar daar ben ik niet in thuis.

 

[Dharwin]

Ik denk dat ik de vraag niet duidelijk heb gesteld, ik zal proberen duidelijker te formuleren:

Het antwoord dat in cel D4 (gele cel) moet komen, is het aantal unieke orders dat de klant (die ingevoerd wordt in cel C3) heeft geplaatst. De antwoorden voor klant 1, 2 en 3 zijn respectievelijk 2, 4 en 2.

De regels 9 t/m 31 zijn orderregels, een order kan meerdere orderregels hebben.

Het eigenlijke bestand waar ik deze formule voor nodig heb, telt meer dan 600.000 orderregels.

 

[Warme bakkertje]

Test deze eens

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Then
        For Each cl In Range("A9:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            If cl = Target.Value And InStr(sq, cl.Offset(, 1).Value) = 0 Then
                sq = sq & "|" & cl.Offset(, 1).Value
            End If
        Next
        [D3] = UBound(Split(sq, "|"))
    End If
End Sub

 

[Dharwin]

Test deze eens

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Then
        For Each cl In Range("A9:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            If cl = Target.Value And InStr(sq, cl.Offset(, 1).Value) = 0 Then
                sq = sq & "|" & cl.Offset(, 1).Value
            End If
        Next
        [D3] = UBound(Split(sq, "|"))
    End If
End Sub

Hoe doe ik dat? (sorry, ben niet echt een expert in excel)

 

[Warme bakkertje]

Rechtsklik op de tab van je werkblad en selecteer Programmacode weergeven.
Plak nu in het rechtervenster de code. Als je nu in C3 het klantnummer wijzigt zou het juiste aantal in D3 moeten verschijnen.

 

[ExcelAmateur]

Hier het bestandje met de code van Warme bakkertje.

 

[Dharwin]

@warme bakkertje
Ik snap niks van wat je hebt opgestuurd maar het werkt fantastisch!
Dank u zeer voor de oplossing:thumb:

ExcelAmateur natuurlijk ook heel erg bedankt voor de moeite:thumb:

 

[Warme bakkertje]

@ EA
Je krijgt toch 4 in D3, zijnde de verschillende unieke orders van klant 2, hetzelfde als TS zegt in zijn antwoord

 

[Niels28]

Kan ook met een formule in D3,
afsluiten met Ctrl-shift-enter

=SOM(ALS(INDIRECT("$B$"&VERGELIJKEN(C3;A9:A31;0)+8&":$B$"&VERGELIJKEN(C3;A9:A31;1)+8)<>"";1/AANTAL.ALS(INDIRECT("$B$"&VERGELIJKEN(C3;A9:A31;0)+8&":$B$"&VERGELIJKEN(C3;A9:A31;1)+8);INDIRECT("$B$"&VERGELIJKEN(C3;A9:A31;0)+8&":$B$"&VERGELIJKEN(C3;A9:A31;1)+8))))

Bereik zul je aan moeten passen.

Klantnummer moet op volgorde staan.

Niels

 

[Warme bakkertje]

Aangezien het gaat om 600.000 regels kan je deze misschien ook eens testen op snelheid

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Then
        sq = Range("A9:B" & Cells(Rows.Count, 1).End(xlUp).Row)
            For i = 1 To UBound(sq)
                If sq(i, 1) = Target.Value And InStr(sn, sq(i, 2)) = 0 Then
                    sn = sn & "|" & sq(i, 2)
                End If
            Next
        [D3] = UBound(Split(sn, "|"))
    End If
End Sub

 

[ExcelAmateur]

Warme bakkertje
@ EA
Je krijgt toch 4 in D3, zijnde de verschillende unieke orders van klant 2, hetzelfde als TS zegt in zijn antwoord

Sorry ik zag het net en heb de opmerking weggehaald.
Soms zijn we te snel met opmerkingen.

 

[Warme bakkertje]

@ Niels28
Uit post#1

N.B. in de echte database is er geen mogelijkheid om de orderregels oplopend of aflopend te selecteren

 

[Niels28]

N.B. in de echte database is er geen mogelijkheid om de orderregels oplopend of aflopend te selecteren

Blijft lastig vraag goed te lezen.

niels

 

[Dharwin]

@WB
Wat zou het verschil moeten zijn tussen de twee macro's? Ze doen het beide, dus ik merk niets

 

[Warme bakkertje]

De 1ste macro leest regel per regel van je werkblad, de 2de macro voert dezelfde bewerking uit, maar dan in het werkgeheugen. Dit zou moeten resulteren in een snellere uitvoering aangezien lezen vanaf het werkblad trager is dan in het werkgeheugen te werken.
Maar als jij geen merkbaar verschil ziet, des te beter. Toch is het mi beter om voor macro2 te gaan.