Unieke waarden tellen in Excel 2016

[Schrikbeeld]

Dag,
Ik heb een beetje hulp nodig voor het tellen van unieke waarden in een kolom met Excel 2016.
Op internet vindt ik allerlei voorbeelden met functies die nog niet in versie 2016 zitten.

Stel ik heb een rij met waarden 1, 2, 3, 1. Dan is het aantal unieke waarden 3.
Het gaat me niet om filters oid. maar om een functie/formule die weer in andere formules kan worden gebruikt.

Alvast dank!

PS. Of kan ik beter upgraden naar een nieuwere versie van Excel? En is dat gratis te upgraden?
Ik houd het liever bij een lokale versie en geen Office 365.

 

[AlexCEL]

Als die waarden 1,2,3,1 in A1 t/m A4 staan, dan kan zoiets in Excel 2016:

=SOMPRODUCT(1/AANTAL.ALS(A1:A4;A1:A4))

Mogelijk afsluiten met Control+Shift+Enter (matrixfunctie). Kan ik niet meer testen...

 

[JEC.]

Is inderdaad een matrix. Deze zal er echter vrij snel voor zorgen dat je bestand traag wordt wanneer de hoeveelheid data toeneemt.
Als dat het geval is, kun je er beter een macro van maken die alleen telt wanneer jij het wil.

 

[HSV]

Gewoon met Enter.
Rekening houdend met evt. lege cellen.

=SOMPRODUCT((A1:A10<>"")/AANTAL.ALS(A1:A10;A1:A10&""))

 

[Schrikbeeld]

Dank allen voor de snelle reacties!
@AlexCel: Deze formule doet inderdaad precies wat ik zoek. In mijn versie van Excel (2016) hoef ik geen Control+Shift+Enter te gebruiken, hij doet het ook zonder.
@JVeer: Hij wordt idd. traag, ik heb best een snelle processor maar met een tabel van 13000 regels duurt het 3 a 4 seconden voor een wijziging is doorgevoerd.
@HSV: Deze formule is helemaal mooi! Lege regels heb ik (nu) niet maar dit maakt de formule universeel, maar dus ook trager.

Misschien kan ik het probleem ook anders aanvliegen door de dubbele regels er eerst uit te filteren, maar dan heb ik weer hulp nodig

Het gaat om een dump van mijn stamboomdatabase in Aldfaer met de onhebbelijke eigenschap dat bv. iemand die ik de kerk is getrouw een extra regel krijgt. Als van deze persoon ook een ondertrouwdatum bekend is komt er een 3e regel bij.
In de voorbeeld-xls is Aaltje Jansen (regel 2) getrouwd op het gemeentehuis waardoor zij 1 regel in het bestand heeft.
Johanna Plats (regels 3, 4 en 5) is in de kerk getrouwd + is een ondertrouwdatum bekend, het stamboomprogramma geeft dan als output 3 regels voor deze ene persoon. Op de 2e regel staat de datum voor het kerkelijk huwelijk met in kolom R (kerk) de waarde -1, en op een 3e regel voor diezelfde persoon staat de ondertrouwdatum met in kolom Q (huw.type) de tekst Ondertrouw.

Als ik zo een bestand heb van 10000 personen dan heb ik al snel 3000 dubbele regels.
Is het mogelijk om van de 3 regels van Johanna Plats 1 regel te maken?

Als dat gelukt is kan je veel makkelijker allerlei berekeningen uitvoeren op de aantallen personen en genoemde datums omdat iedere persoon gewoon 1 regel is.

PS. De namen en data in het voorbeeldbestand zijn gefingeerd, dit om aan de regels voor AVG en dit forum te voldoen.

 

[JEC.]

Op je eerste voorbeeld gebaseerd. Hang deze dan achter een knop en run wanneer je zelf wil. Blijft je werkblad snel

Sub jec()
 Dim ar, sq As Variant, i As Long
 ar = Range("A1:A10000")
 
 With CreateObject("scripting.dictionary")
   For i = 1 To UBound(ar)
     sq = .Item(ar(i, 1))
   Next
  Range("B1") = .Count
 End With
End Sub

 

[HSV]

De variabele sq is niet nodig bij:

.Item(ar(i, 1)) = ""

 

[peter59]

Hallo,

Je eerste bestandje zie ik nergens meer dus maar met de Aldfaerdump gedaan.
In jou voorbeeld heel eenvoudig met Power Query.

 

[Schrikbeeld]

Beste reageerders,

ik heb opgelost met de macro. De oplossing met de matrixfunctie duurt nu al lang en ik verwacht dat mijn bestand nog flink zal groeien.
Met dank aan allen en sorry voor mijn late afmelding, een ouderwets griepje zat in de weg.

peter.