Combinatie

Dubbele factuurnummers in twee exports

Zet twee maandelijkse inkoopexports onder elkaar en bewaar elk factuurnummer dat vaker dan één keer voorkomt, met VERT.STAPELEN, UNIEK, X.VERGELIJKEN, ISNB en FILTER.

Gebruikte functies:VERT.STAPELENUNIEKFILTERX.VERGELIJKENISNBExcel 2024 of nieuwer

Moeilijkheidsgraad

Uitgewerkt voorbeeld

Factuurnummers die vaker dan één keer voorkomen in de exports van januari en februari

Situatie

Bij je werkzaamheden op de inkoopcyclus van Veldhuis Installatie BV kreeg je de inkoopfactuurnummers van januari en februari als twee aparte exports. Je wilt één lijst van elk factuurnummer dat in beide maanden samen vaker dan één keer voorkomt, zodat je kunt nagaan of een factuur dubbel is geboekt.

De gegevens

ABCDE
1JanuariFebruari
225-041225-042025-0420=FILTER(VERT.STAPELEN(A2:A7, B2:B7), ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7, B2:B7), UNIEK(VERT.STAPELEN(A2:A7, B2:B7), , WAAR))), "Geen dubbelen")=FILTER(VERT.STAPELEN(A2:A7; B2:B7); ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7; B2:B7); UNIEK(VERT.STAPELEN(A2:A7; B2:B7); ; WAAR))); "Geen dubbelen")
325-041525-044625-0433
425-042025-043325-0420
525-043325-045225-0433
625-043725-042025-0420
725-044125-0458
8
Formulecel (D2)Overloopresultaat

Formule in D2

=FILTER(VERT.STAPELEN(A2:A7, B2:B7), ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7, B2:B7), UNIEK(VERT.STAPELEN(A2:A7, B2:B7), , WAAR))), "Geen dubbelen")
=FILTER(VERT.STAPELEN(A2:A7; B2:B7); ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7; B2:B7); UNIEK(VERT.STAPELEN(A2:A7; B2:B7); ; WAAR))); "Geen dubbelen")

Resultaat: 25-0420 / 25-0433 / 25-0420 / 25-0433 / 25-042025-0420 / 25-0433 / 25-0420 / 25-0433 / 25-0420

Hoe de onderdelen samenwerken

Let op: Factuurnummers die alleen verschillen door een spatie aan het eind, of doordat het ene tekst en het andere een getal is, tellen als verschillend, waardoor een echt duplicaat ontsnapt. Maak de exports eerst schoon met SPATIES.WISSEN als ze uit verschillende systemen komen.

De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.

  1. =VERT.STAPELEN(A2:A7, B2:B7)
    =VERT.STAPELEN(A2:A7; B2:B7)

    Zet de januari-export boven de februari-export: één kolom van 12 factuurnummers, van 25-0412 tot 25-0458. Er is nog niets verwijderd; 25-0420 komt drie keer voor en 25-0433 twee keer.

  2. =UNIEK(VERT.STAPELEN(A2:A7, B2:B7), , WAAR)
    =UNIEK(VERT.STAPELEN(A2:A7; B2:B7); ; WAAR)

    Met het derde argument WAAR geeft UNIEK alleen de waarden die precies één keer voorkomen.

    Van de 12 nummers vallen 25-0420 (3 keer) en 25-0433 (2 keer) af, dus er blijven er 7 over: 25-0412, 25-0415, 25-0437, 25-0441, 25-0446, 25-0452 en 25-0458.

  3. =X.VERGELIJKEN(VERT.STAPELEN(A2:A7, B2:B7), UNIEK(VERT.STAPELEN(A2:A7, B2:B7), , WAAR))
    =X.VERGELIJKEN(VERT.STAPELEN(A2:A7; B2:B7); UNIEK(VERT.STAPELEN(A2:A7; B2:B7); ; WAAR))

    Zoekt elk van de 12 gestapelde nummers op in de lijst van 7. Een nummer dat één keer voorkomt krijgt zijn positie (1, 2, 3, ...); een herhaald nummer staat niet in de lijst en geeft #N/B. Het resultaat is 1, 2, #N/B, #N/B, 3, 4, #N/B, 5, #N/B, 6, #N/B, 7.

  4. =ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7, B2:B7), UNIEK(VERT.STAPELEN(A2:A7, B2:B7), , WAAR)))
    =ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7; B2:B7); UNIEK(VERT.STAPELEN(A2:A7; B2:B7); ; WAAR)))

    Maakt van elke #N/B WAAR en van elke positie ONWAAR: ONWAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, WAAR, ONWAAR, WAAR, ONWAAR.

    De WAAR-waarden staan op plek 3, 4, 7, 9 en 11, de herhaalde facturen.

  5. =FILTER(VERT.STAPELEN(A2:A7, B2:B7), ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7, B2:B7), UNIEK(VERT.STAPELEN(A2:A7, B2:B7), , WAAR))), "Geen dubbelen")
    =FILTER(VERT.STAPELEN(A2:A7; B2:B7); ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7; B2:B7); UNIEK(VERT.STAPELEN(A2:A7; B2:B7); ; WAAR))); "Geen dubbelen")

    FILTER bewaart de gestapelde nummers op de WAAR-plekken: 25-0420, 25-0433, 25-0420, 25-0433, 25-0420. Dat zijn 5 rijen, één voor elke keer dat een herhaalde factuur voorkomt. Komt niets dubbel voor, dan toont de cel de tekst voor geen dubbelen.

Stap voor stap

  1. VERT.STAPELEN(A2:A7; B2:B7) zet februari onder januari: 6 + 6 = 12 nummers. Plek 1 tot en met 6 zijn 25-0412, 25-0415, 25-0420, 25-0433, 25-0437, 25-0441; plek 7 tot en met 12 zijn 25-0420, 25-0446, 25-0433, 25-0452, 25-0420, 25-0458.
  2. Het aantal keren dat elk nummer voorkomt: 25-0420 staat op plek 3, 7 en 11 (3 keer), 25-0433 op plek 4 en 9 (2 keer) en de andere 7 nummers elk één keer. Controle: 3 + 2 + 7 = 12.
  3. UNIEK(...; ; WAAR) bewaart alleen de nummers die precies één keer voorkomen: 25-0412, 25-0415, 25-0437, 25-0441, 25-0446, 25-0452, 25-0458. Dat zijn 7 nummers.
  4. X.VERGELIJKEN zoekt alle 12 gestapelde nummers op in die 7: 1, 2, #N/B, #N/B, 3, 4, #N/B, 5, #N/B, 6, #N/B, 7. De herhaalde nummers staan niet in de lijst en geven daarom #N/B.
  5. ISNB maakt daarvan ONWAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, WAAR, ONWAAR, WAAR, ONWAAR. WAAR markeert plek 3, 4, 7, 9 en 11.
  6. FILTER geeft de gestapelde nummers op die plekken terug: 25-0420, 25-0433, 25-0420, 25-0433, 25-0420. De lijst loopt vanaf D2 door tot D6, 5 rijen.

Waarom het werkt

UNIEK met het derde argument WAAR geeft de nummers die in orde zijn, de nummers die één keer voorkomen. Alles in de stapel wat niet in die lijst staat, komt vaker dan één keer voor, en X.VERGELIJKEN met ISNB maakt van die vraag per rij WAAR of ONWAAR. FILTER bewaart daarna de rijen met WAAR.

Omdat VERT.STAPELEN een matrix geeft en geen bereik, werkt elke stap in één keer op de hele stapel, zonder hulpkolom.

Let op

  • AANTAL.ALS kan dit niet op de gestapelde lijst. AANTAL.ALS wil als eerste argument een bereik en VERT.STAPELEN geeft een matrix, dus =AANTAL.ALS(VERT.STAPELEN(A2:A7; B2:B7); A2) geeft een foutmelding. Daarom gebruikt de constructie UNIEK en X.VERGELIJKEN, die wel matrices accepteren.
  • Het resultaat toont elke keer dat een herhaalde factuur voorkomt, niet elke factuur één keer: 25-0420 staat er drie keer in omdat het drie keer is geëxporteerd. Zet UNIEK rond de formule als je één regel per factuurnummer wilt.

Varianten

=UNIEK(FILTER(VERT.STAPELEN(A2:A7, B2:B7), ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7, B2:B7), UNIEK(VERT.STAPELEN(A2:A7, B2:B7), , WAAR))), "Geen dubbelen"))
=UNIEK(FILTER(VERT.STAPELEN(A2:A7; B2:B7); ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7; B2:B7); UNIEK(VERT.STAPELEN(A2:A7; B2:B7); ; WAAR))); "Geen dubbelen"))

UNIEK om de hele formule: één regel per herhaalde factuur, dus 25-0420 en 25-0433 (2 rijen in plaats van 5).

=FILTER(A2:A7, AANTAL.ALS(A2:B7, A2:A7) > 1, "Geen dubbelen")
=FILTER(A2:A7; AANTAL.ALS(A2:B7; A2:A7) > 1; "Geen dubbelen")

De oudere aanpak met AANTAL.ALS op het echte bereik A2:B7, zonder VERT.STAPELEN. Het telt elk januarinummer in beide kolommen (25-0420 geeft 3, 25-0433 geeft 2, de rest 1) en geeft 25-0420 en 25-0433 terug. Het toont alleen de januarikant en vraagt dat de gegevens in één blok cellen staan.

Alle combinaties