Excel Unieke waarden weergeven

 

Het uitfilteren van unieke waarden uit een kolom met gegevens kan in Excel bijvoorbeeld met matrixformules. Stel een kolom in Excel bevat onderstaande gegevens:

unieke_waarden_weergeven1_215x370

Om nu in kolom B de unieke waarden van de gegevens van kolom A weer te geven, kan gebruik worden gemaakt van de volgende matrixformule in cel B2:

{=ALS.FOUT(INDEX(A$2:A$15;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(A$2:A$15;$B$1:B1;0));0));"")}

(NB1: Vervang bij Engelstalige Excel-versies ALS.FOUT door IFERROR, VERGELIJKEN door MATCH, ONWAAR door FALSE en ISGETAL door ISNUMBER)

(NB2: voor Excel-versie 2003 of eerder moet de formule zijn: {=ALS(ISFOUT(INDEX(A$2:A$15;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(A$2:A$15;$B$1:B1;0));0)));"";INDEX(A$2:A$15;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(A$2:A$15;$B$1:B1;0));0)))}  )

Deze formule moet zonder accolades worden ingegeven en moet afgesloten worden met CTRL+SHIFT+ENTER. Daarna verschijnen automatisch de accolades, ten teken dat het om een matrixformule gaat. De formule kan daarna naar beneden gekopieerd worden. Daarna ontstaat het volgende overzicht:

unieke_waarden_weergeven2_215x370

Te zien is dat in kolom B alleen de unieke waarden uit kolom A worden weergegeven.

Tenslotte een korte uitleg over de werking van de gebruikte formule. Het gaat om een geneste matrixfunctie waarvan de werking in woorden lastig is uit te leggen. Het komt er in het kort op neer dat de laatste VERGELIJKEN-functie zoekt naar waarden van de bronkolom in de bovenliggende rijen. De eerste VERGELIJKEN-functie zoekt naar de eerste positie, waarop de laatste VERGELIJKEN-functie niets kan vinden en dus ISGETAL onwaar wordt. Vervolgens wordt met de INDEX-functie de waarde van deze positie weergegeven. Als de INDEX-functie een foutmelding geeft omdat alle waarden al gevonden zijn, dan wordt een lege cel weergegeven.

Meer weten over matrixformules? Kijk dan op onze tutorials Matrixformules (basis) of Matrixformules (vervolg).

 

Interesse in andere tips & trucs? Ga dan naar ons tips&trucs-overzicht of neem een kijkje op onze pagina met tutorials.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

29 reacties op Excel unieke waarden weergeven

  • Beste,

    Ik heb de formule toegepast en deze werkt niet helemaal! In mijn geval moet ik een unieke lijst maken uit 5500 records. Het geval wil dat ik na 21 records een dubbele vermelding krijg en na 23 een 3-voudige melding.

    Heeft u een idee hoe dit kan komen?

    • Dag Tjeerd,
      Lastig te beoordelen zonder het bestand te zien. Oorzaak kan zijn dat de bereiken van de formule niet goed zijn ingesteld of dat ogenschijnlijk dubbele vermeldingen in werkelijkheid toch iets verschillen (bijvoorbeeld door een verschil in spaties).
      Groeten, Manfred

      • Beste manfred,

        Ik heb de formule toegepast en hij werkt alleen wil ik een extra criterium bereik toevoegen.
        Alle namen staan bij mij in kolom B en in kolom A staan 4 categorieën waarin de namen zijn onderverdeeld.
        De formule moet in kolom c alle unieke waarden laten zien van categorie 1 en in kolom B unieke waarden van categorie 2 etc…. Is dit mogelijk?

        Groet,
        Noah

        • Beste Noah,
          Ja dat is mogelijk. Dat kan bijvoorbeeld met de volgende formule:

          =ALS.FOUT(INDEX(B$2:B$12;KLEINSTE(ALS(A$2:A$12=$F$1;ALS(ISNB(VERGELIJKEN(B$2:B$12;C$1:C1;0));RIJ(B$2:B$12)));1)-1);"")

          Ik ga er hierbij vanuit de formule wordt geplaatst in C2 en dat de categorienaam in F1 staat.
          Groeten,
          Manfred

  • Mooie formule! Werkt bij mij ook. Alleen: als de eerste lijst een variabele lengte heeft en ik maak het gebied groot genoeg (dus bijv. A2:A100, dan krijg ik in de unieke lijst ook steeds de waarde 0 vanwege lege cellen in A2:A100. Hoe zorg ik ervoor dat die 0 niet getoond wordt? Ik wil de unieke lijst graag gebruiken voor een keuzelijst in een ander sheet.

    • Dat kan bijvoorbeeld met de volgende matrixformule:

      =ALS.FOUT(INDEX(A$2:A$100;KLEINSTE(ALS(A$2:A$100<>"";ALS(ISNB(VERGELIJKEN(A$2:A$100;B$1:B1;0));RIJ(A$2:A$100)-1));1));"")
  • Beste,

    Ik probeer in kolom B het volgende te laten tellen: het aantal B’tjes, D’tjes en P’tjes. Echter staat er in kolom C een code. De code komt een aantal keer voor in deze kolom, maar zal altijd gekoppeld zijn aan een B, D of P nooit anders. Hoe kan ik dan het aantal B, D, en P laten berekenen gekoppeld aan de code? Zodat ik onder aan de lijst een totaal krijg?

    Ik hoop dat ik hiermee duidelijk genoeg ben geweest.

    Groet Ruud

    • Beste Ruud,
      Als ik het goed begrijp wil je alleen de B, D en P tellen waar in kolom C een code staat (en kolom C dus niet leeg is)? Test dan deze formule eens:

      =SOMPRODUCT(((B2:B100="B")+(B2:B100="D")+(B2:B100="P"))*(C2:C100<>""))

      Groeten Manfred

  • Beste Manfred,

    Op basis van uw verhaal ben ik tot onderstaande werkende formule gekomen:
    =ALS.FOUT(INDEX(C$2:C$280;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(C$2:C$280;$L$1:L1;0));0));””)

    Allereerst dank voor uw uitleg. Nu pas ik deze formule niet toe op enkel gevulde cellen, een deel van het bereik (C250 tot C280) is leeg, zodat deze later nog kan worden aangevuld.
    Nu geeft de formule aan het einde van de reeks nog een 0 vanwege de lege cellen. Ik zou graag willen dat er geen uitkomst 0 meer is in de unieke lijst, is dit mogelijk? Hoe zou ik de formule moeten aanpassen om geen 0 meer te krijgen?

    Groeten,

    Ronald

    • Beste Ronald,
      Bedankt voor je reactie.
      Om een nul te voorkomen kun je bijvoorbeeld deze matrixformule gebruiken (te plaatsen in cel L2):

      =ALS.FOUT(INDEX(C$2:C$280;KLEINSTE(ALS(C$2:C$280<>"";ALS(ISNB(VERGELIJKEN(C$2:C$280;L$1:L1;0));RIJ(A$2:A$280)-1));1));"")

      Groeten, Manfred

  • Beste Manfred,

    Ik ben op zoek naar een dergelijke formule, maar dan voor een tabel. In het bereik $D$8:$Y$36 vul ik een aantal waarden in, meestal ongeveer 15 verschillende lettercodes. Graag wil ik een lijst van alle unieke waarden in dit bereik in een rij (!) hebben staan, zodat ik daarna het aantal keer dat de specifieke lettercode voorkomt kan tellen per rij van het bereik. Het bereik bevat ook lege cellen. Kun je me hiermee helpen?

    Alvast bedankt,

    • Beste Jan-Peter,
      Omdat het in dit geval om unieke waarden uit een tabel gaat, zou ik voor een VBA-oplossing kiezen. Bijvoorbeeld:

      Private Sub Worksheet_Change(ByVal Target As Range)
           Dim cl As Range, rng As Range
           If Not Intersect(Target, Range("D8:Y36")) Is Nothing Then
              Rows(1).ClearContents
              Set rng = Range("D8:Y36").SpecialCells(2)
              With CreateObject("scripting.dictionary")
                  For Each cl In rng
                      .Item(cl.Value) = ""
                  Next
                  [A1].Resize(, .Count) = .keys
              End With
           End If
      End Sub

      De unieke waarden worden daarbij in rij 1 gezet.

      • Beste Manfred,

        Hartelijk dank voor je snelle reactie. Het werkt prima.
        Mijn ervaring met VBA is niet zo groot dat ik de code volledig begrijp.
        Wat zou ik moeten veranderen als ik de resultaten niet in een rij, maar in een kolom wil hebben, beginnend in AA7?

        Alvast hartelijk dank

        • Dag Jan-Peter,
          De code wordt dan:

          Private Sub Worksheet_Change(ByVal Target As Range)
               Dim cl As Range, rng As Range
               If Not Intersect(Target, Range("D8:Y36")) Is Nothing Then
                  Columns(27).ClearContents
                  Set rng = Range("D8:Y36").SpecialCells(2)
                  With CreateObject("scripting.dictionary")
                      For Each cl In rng
                          .Item(cl.Value) = ""
                      Next
                      [aa7].Resize(.Count) = Application.Transpose(.keys)
                  End With
               End If
          End Sub
          • Beste Manfred,

            Hartelijk dank voor je reactie.
            Ik begrijp hieruit dat je kolom AA eerst volledig leegt als er al iets in staat. Maar wat als er waarden staan die moeten blijven staan? Bijvoorbeeld in cel AA6.

            Alvast bedankt

            • Volgens mij ben ik er al uit, door niet Column(27), maar Range(“AA7:AA..”) te gebruiken. Nogmaals bedankt!

  • Best Manfred ,
    Ik heb een Exel sheet met diverse recepten .
    een tab blad (recept) bevat de ingrediënten van een bepaald product bv cake , slagroom taart soezen etc. en de hoeveelheden die nodig zijn voor de bereiding van elk recept.
    Nu wil ik graag een tabblad met een overzicht van de recepten die ik ga bereiden
    In de linker kolom komt het ingrediënt te staan en in de rij boven het product bv cake, soezen.
    vraag is het mogelijk een dergelijk overzicht te maken in EXEL ? En hoe kan ik dat voor elkaar krijgen.
    Het overzicht maken van bepaalde tab bladen bv nu voor Cake en soezen en daarna evt een overzicht met meerdere tabbladen (recepten) dus variabel. Ik ben hier al een aantal maal mee bezig geweest door eerst alle waarden van alle tabladen verticaal te zoeken en vervolgens in kolom 2 alleen de unieke waarden uit filteren maar het sorteren lukt niet automatisch.
    De tabel zou er als volgt uit kunnen zien
    In de linker kolom komen de ingrediënten voorafgaand met de product code te staan maar deze wil ik graag gesorteerd hebben per groep en vervolgens op ingrediënt( product code) zodat elke product code (ingrediënt) maar 1 x voorkomt
    Een groep kan zijn meel (A) , boter en olie (B) , overige (c)
    Deze tabel bevat alleen de ingredienten van de recepten waar van een overzicht gemaakt word
    Zodat ik met deze tabel vervolgens kan zien hoeveel kg ik van een bepaald ingrediënt nodig heb en wat de kosten zijn en de totaal kosten
    Ik heb ook een tabblad met de ingrediënten deze bevat de product code naam eenheid prijs etc.

    Voorbeeld lijst met ingrdienten
    Code naam groep naam groep
    101 tarwemeel A meel
    102 roggemeel A meel
    201 roomboter B boter en olie
    202 margarine B boter en olie
    301 slagroom C overige

    Al vast bedankt
    Ronald

    • Beste Ronald,
      Ik denk dat het goed mogelijk is om te maken wat jij wilt. Hoewel je de opbouw van het bestand uitgebreid hebt beschreven, is het me nog niet 100% duidelijk. Uit ervaring kan ik zeggen dat de exacte werking en opbouw van een bestand in woorden omschrijven erg moeilijk is. Het sturen van een representatief voorbeeldbestand zou wat dat betreft veel helpen. Ik wil dan wel even vrijblijvend kijken naar de mogelijkheden om jouw wensen te realiseren. Op mijn contactpagina kun je mijn mailadres vinden.
      Groeten, Manfred

  • Beste Manfred,

    Onderstaande formule gebruik ik met veel plezier om steeds mooie dropdownmenu’s te maken met opeenvolgende unieke waarden. Is het echter ook mogelijk om hetzelfde te creëren, maar dan met om de 4 regels een witregel er tussen? Dan geeft wat meer overzicht bij enorme lange lijsten.

    =ALS.FOUT(INDEX(C$2:C$280;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(C$2:C$280;$L$1:L1;0));0));””)

    Ik hoor graag van je,

    Groeten,
    Kees

    • Beste Kees,

      Als je jouw formule als volgt aanpast, dan wordt om de 4 rijen een regel overgeslagen:

      =ALS.FOUT(ALS(REST(RIJ()-1;5)=0;"";INDEX(C$2:C$280;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(C$2:C$280;$L$1:L1;0));0)));"")

      Groeten, Manfred

  • Dag manfred,

    ik zit met een gelijkaardig probleem, ik heb een sheet waarbij kolom A en B ingevuld is 300 rijen diep, echter staan er ook 0 waardes is deze rijen.

    ik wil er nu alle unieke combinatie waardes uithalen en plaatsen in kolom E & F
    vb
    A B C E F G
    3D 18 500 3d 18 600
    3d 24 200 3d 24 350
    3d 18 100
    3d 24 150 2d 18 200
    0 0 0 2d 24 250
    2d 18 200
    2d 24 100
    2d 24 150
    0

    als laatste wil ik dan weer in kolom g de som plaatsen van de waarden in kolom C die overeen komen met de lijnen in kolom E&F

    nen ferme boterham 🙂 ik kan het via de klassieke formules wel enigsinds een gelijkaardig resultaat bekomen, maar er gaan zoveel hulp kolommen in dat het niet overzichtelijk meer is.

    • Dag Benito,
      Ik denk dat je met één hulpkolom een heel eind moet komen. Om je op weg te helpen: stel de data staat in A2:C9 dan zou je in hulpkolom D vanaf cel D2 de volgende formule kunnen plaatsen:

      =ALS.FOUT(INDEX(A$2:A$9&B$2:B$9;KLEINSTE(ALS(ISNB(VERGELIJKEN(A$2:A$9&B$2:B$9;D$1:D1;0));RIJ(A$2:A$9)-1);1));"")
      • Dag Manfred,
        Dank u wel voor de formule, klopt inderdaad nu heb ik een opsomming van alle unieke gegevens uit kolom a & b.
        samengevoegd in 1 kolom (D).

        als ik nu inde kolom E naast iedere unieke waarde de som wens van alle overeenkomstige waarden uit kolom C, dan dien ik een gelijkaardige formule te bouwen maar dan met sumproduct?

        Alvast Bedankt!

        • Dag Benito,

          Dat kan bijvoorbeeld met de volgende formule:

          =SOMMEN.ALS(C$2:C$9;A$2:A$9;LINKS(D2;2);B$2:B$9;RECHTS(D2;2))
          • Dag Manfred,

            Opnieuw bedankt met uw formule, aan de hand van alles wat ik ondertussen heb gelezen via uw documentatie betreffende matrixen heb ik men huidige versie van een zeer uitgebreide excel file(die nog niet klaar is) voor berekeningen met en over verschillende tabbladen (en variablelen) kunnen terug brengen naar 3.9Mb grootte ipv de 9,2Mb voorheen 🙂 Waarvoor Dank!! Wetende hoeveel bijkomende lijsten en berekeningen ik nog dien toe te voegen, ben ik zeer blij met de datacapaciteits winst!! en het leren van matrixen 🙂

            Ivm de Sommen.als formule:
            op een bepaald moment stopte het samentellen van gegevens, niet begrijpende waarom, heb ik het nu toch gevonden, -> Links(D2;2) spreekt over de eerste 2 karakters links beginnend in kolom d rij 2 (vermoed ik), Echter na 3d en 2d komt de afkorting COM en daarna SPI en dan NT be16, be14 (om maar een paar voorbeelden te geven)
            afkortingen van 3deurs, 2deurs, commode, spiegel, nachttafel. bed160, bed140, etc

            kunnen we de linkse kant gegevenslengte variabel maken? wetende dat de laatste 2 karakters (rechts dus) steeds 2 karakters zijn, als in 3d18, 3d24, 2d18, 2d24, com18, com24, spi18, spi24, nt18, nt24, be1618,be1624, etc

            dus de laatste karakters spreken steeds op een dikte waarbij dit steeds 2 karakters is.

            Alvast Bedankt voor de hulp!

            • Hallo Benito,

              Mooi dat de formules je hebben geholpen. Om de formule variabel te maken kun je het gedeelte in de formule met LINKS vervangen door LINKS(D2;LENGTE(D2)-2). Hiermee zou het moeten lukken.

              • Dag Manfred,

                Schitterend! Dank U wel, hiermee is het inderdaad gelukt! helpt me een hele pak gemakkelijke vooruit 🙂

                mvg
                benito

  • Beste Manfred,

    Ik wil in Excel 2016 blijvend voorkomen dat in kolom A dubbele nummers toegevoegd worden, hoe bereik ik dat?

    • Dag Peter,
      Bij tips & trucs heb ik daar een speciaal onderwerp aan gewijd: dubbele waarden verhinderen. Wellicht helpt dat je verder.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.