Ga naar hoofdinhoud

Hoe unieke waarden uit meerdere kolommen in Excel te extraheren?

Stel dat u meerdere kolommen met meerdere waarden heeft, sommige waarden worden in dezelfde kolom of in een andere kolom herhaald. En nu wilt u de waarden vinden die in beide kolommen slechts één keer voorkomen. Zijn er snelle trucs om unieke waarden uit meerdere kolommen in Excel te extraheren?


Extraheer unieke waarden uit meerdere kolommen met formules

In deze sectie worden twee formules behandeld: de ene gebruikt een matrixformule die geschikt is voor alle Excel-versies, en de andere gebruikt een dynamische matrixformule specifiek voor Excel 365.

Extraheer unieke waarden uit meerdere kolommen met de matrixformule voor alle Excel-versies

Voor gebruikers met elke versie van Excel kunnen matrixformules een krachtig hulpmiddel zijn voor het extraheren van unieke waarden over meerdere kolommen. Hier ziet u hoe u het kunt doen:

1. Ervan uitgaande dat uw waarden binnen bereik zijn A2: C9, voer de volgende formule in cel E2 in:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
Note: In de bovenstaande formule, A2: C9 geeft het celbereik aan waarvan u de unieke waarden wilt extraheren, E1: E1 is de eerste cel van de kolom waarin u het resultaat wilt plaatsen, $ 2: $ 9 staat voor de rijen die de cellen bevatten die u wilt gebruiken, en $ A: $ C geeft aan dat de kolommen de cellen bevatten die u wilt gebruiken. Wijzig ze in uw eigen.

2. Druk vervolgens op Shift+Ctrl+Enter toetsen samen en sleep vervolgens de vulgreep om de unieke waarden te extraheren totdat er lege cellen verschijnen. Zie screenshot:

Verklaring van deze formule:
  1. $ A $ 2: $ C $ 9: dit specificeert het gegevensbereik dat moet worden gecontroleerd, namelijk de cellen van A2 tot C9.
  2. IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
    • $A$2:$C$9<>"" controleert of de cellen in het bereik niet leeg zijn.
    • COUNTIF($E$1:E1,$A$2:$C$9)=0 bepaalt of de waarden van deze cellen nog niet zijn vermeld in het celbereik van E1 tot E1.
    • Als aan beide voorwaarden is voldaan (dwz de waarde is niet leeg en nog niet vermeld in kolom E), berekent de IF-functie een uniek getal op basis van de rij en kolom (ROW($2:$9)*100+COLUMN($A: $C)).
    • Als niet aan de voorwaarden wordt voldaan, retourneert de functie een groot getal (7^8), dat als tijdelijke aanduiding dient.
  3. MIN(...): Vindt het kleinste getal dat wordt geretourneerd door de IF-functie hierboven, overeenkomend met de locatie van de volgende unieke waarde.
  4. TEKST(...,"R0C00"): Converteert dit minimumaantal naar een adres in R1C1-stijl. De formaatcode R0C00 geeft de conversie van het getal naar het Excel-celreferentieformaat aan.
  5. INDIRECTE(...): Gebruikt de INDIRECT-functie om het in de vorige stap gegenereerde adres in R1C1-stijl terug te converteren naar een normale celverwijzing in A1-stijl. De functie INDIRECT maakt celverwijzingen mogelijk op basis van de inhoud van een tekstreeks.
  6. &"": Het toevoegen van &"" aan het einde van de formule zorgt ervoor dat de uiteindelijke uitvoer als tekst wordt behandeld, zodat zelfs getallen als tekst worden weergegeven.
 
Extraheer unieke waarden uit meerdere kolommen met formule voor Excel 365

Excel 365 ondersteunt dynamische matrices, waardoor het veel eenvoudiger wordt om unieke waarden uit meerdere kolommen te extraheren:

Voer de volgende formule in of kopieer deze naar een lege cel waar u het resultaat wilt plaatsen, en klik vervolgens op Enter sleutel om alle unieke waarden in één keer te krijgen. Zie schermafbeelding:

=UNIQUE(TOCOL(A2:C9,1))


Extraheer unieke waarden uit meerdere kolommen met Kutools AI Aide

De kracht van Kutools AI-assistent om naadloos unieke waarden uit meerdere kolommen in Excel te extraheren. Met slechts een paar klikken doorzoekt deze intelligente tool uw gegevens en identificeert en vermeldt unieke vermeldingen binnen elk geselecteerd bereik. Vergeet het gedoe van complexe formules of vba-code; omarm de efficiëntie van Kutools AI-assistent en transformeer uw Excel-workflow in een productievere en foutloze ervaring.

Note: Om dit te gebruiken Kutools AI-assistent of Kutools for Excel, Dan kunt u download en installeer Kutools voor Excel kopen.

Klik na het installeren van Kutools voor Excel op Kutools AI > AI-assistent om de te openen Kutools AI-assistent paneel:

  1. Typ uw vereiste in het chatvenster en klik Verzenden knop of druk op Enter toets om de vraag te verzenden;
    "Extraheer unieke waarden uit het bereik A2:C9, waarbij u lege cellen negeert, en plaats de resultaten vanaf E2:"
  2. Klik na het analyseren Uitvoeren knop om te rennen. Kutools AI Aide verwerkt uw verzoek met behulp van AI en retourneert de resultaten rechtstreeks in Excel in de opgegeven cel.


Extraheer unieke waarden uit meerdere kolommen met draaitabel

Als u bekend bent met de draaitabel, kunt u de unieke waarden eenvoudig uit meerdere kolommen extraheren met de volgende stappen:

1. Voeg eerst een nieuwe lege kolom in links van uw gegevens, in dit voorbeeld zal ik kolom A invoegen naast de originele gegevens.

2. Klik op een cel in uw gegevens en druk op Alt + D toetsen en druk vervolgens op P toets onmiddellijk om het te openen Draaitabel en draaigrafiekwizard, kiezen Meerdere consolidatiebereiken zie screenshot in de wizard step1:

3. Dan klikken Volgende knop, controleer Maak een veld met één pagina voor mij optie in wizard step2, zie screenshot:

4. Blijf klikken Volgende knop, klik om het gegevensbereik te selecteren dat de linker nieuwe kolom met cellen omvat, en klik vervolgens op Toevoegen knop om het gegevensbereik toe te voegen aan het Alle reeksen keuzelijst, zie screenshot:

5. Na het selecteren van het gegevensbereik, gaat u verder met klikken Volgende, kies in stap 3 van de wizard waar u het draaitabelrapport wilt plaatsen.

6. Eindelijk, klik Finish om de wizard te voltooien en er is een draaitabel gemaakt in het huidige werkblad en schakel vervolgens alle velden uit Kies velden om toe te voegen aan rapport sectie, zie screenshot:

7. Controleer dan het veld Waarde of sleep de waarde naar het Rijen label, nu krijgt u de unieke waarden uit de meerdere kolommen als volgt:


Extraheer unieke waarden uit meerdere kolommen met VBA-code

Met de volgende VBA-code kunt u ook de unieke waarden uit meerdere kolommen extraheren.

1. Houd de ALT + F11 toetsen, en het opent de Microsoft Visual Basic for Applications-venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het modulevenster.

VBA: extraheer unieke waarden uit meerdere kolommen

Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

3. Druk vervolgens op F5 om deze code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren het gegevensbereik te selecteren dat u wilt gebruiken. Zie screenshot:

4. En klik vervolgens op OK, verschijnt er een ander promptvenster om u een plaats te laten kiezen om het resultaat te plaatsen, zie screenshot:

5. Klik OK om dit dialoogvenster te sluiten, en alle unieke waarden zijn in één keer geëxtraheerd.


Meer relatieve artikelen:

  • Tel het aantal unieke en onderscheidende waarden uit een lijst
  • Stel dat u een lange lijst met waarden heeft met enkele dubbele items, nu wilt u het aantal unieke waarden tellen (de waarden die slechts één keer in de lijst voorkomen) of verschillende waarden (allemaal verschillende waarden in de lijst, het betekent uniek waarden + 1e dubbele waarden) in een kolom zoals het linker screenshot wordt getoond. In dit artikel zal ik het hebben over hoe ik deze taak in Excel kan aanpakken.
  • Extraheer unieke waarden op basis van criteria in Excel
  • Stel dat u het volgende gegevensbereik heeft waarvan u alleen de unieke namen van kolom B wilt weergeven op basis van een specifiek criterium van kolom A om het resultaat te krijgen zoals onderstaand screenshot. Hoe kunt u deze taak snel en gemakkelijk in Excel afhandelen?
  • Sta alleen unieke waarden toe in Excel
  • Als u alleen unieke waarden wilt behouden die in een kolom of werkblad worden ingevoerd en duplicaten wilt voorkomen, zal dit artikel enkele snelle trucs voor u introduceren om met deze taak om te gaan.
  • Som unieke waarden op op basis van criteria in Excel
  • Ik heb bijvoorbeeld een gegevensbereik dat de kolommen Naam en Bestelling bevat, nu om alleen unieke waarden in de kolom Bestelling op te tellen op basis van de kolom Naam, zoals in het volgende screenshot. Hoe los ik deze taak snel en gemakkelijk op in Excel?

Beste Office-productiviteitstools

🤖 Kutools AI-assistent: Een revolutie teweegbrengen in de data-analyse op basis van: Intelligente uitvoering   |  Genereer code  |  Aangepaste formules maken  |  Analyseer gegevens en genereer grafieken  |  Roep Kutools-functies aan...
Populaire functies: Zoek, markeer of identificeer duplicaten   |  Verwijder lege rijen   |  Combineer kolommen of cellen zonder gegevens te verliezen   |   Ronde zonder formule ...
Super opzoeken: Meerdere criteria VLookup    VLookup met meerdere waarden  |   VOpzoeken over meerdere bladen   |   Fuzzy opzoeken ....
Geavanceerde vervolgkeuzelijst: Maak snel een vervolgkeuzelijst   |  Afhankelijke vervolgkeuzelijst   |  Multi-select vervolgkeuzelijst ....
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  |  Vergelijk bereiken en kolommen ...
Uitgelichte functies: Raster focus   |  Ontwerpweergave   |   Grote formulebalk    Werkmap- en bladbeheer   |  resource Library (Auto-tekst)   |  Datumkiezer   |  Combineer werkbladen   |  Cellen coderen/decoderen    Stuur e-mails per lijst   |  Super filter   |   Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen, ...)   |   50+ tabel Types (Gantt Chart, ...)   |   40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag, ...)   |   19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad, ...)   |   12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion, ...)   |   7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Gespleten cellen, ...)   |   ... en meer

Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen.  Klik hier om de functie te krijgen die u het meest nodig heeft...

Omschrijving


Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
Comments (31)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Thank you for this solution, however what if the columns are in separate excel sheet? or is in separate columns instead of a table?
This comment was minimized by the moderator on the site
Hello, Jon,
The methods in this article ar only works well for a range of data, if your data in separate columns, you should copy and paste them into one range first, and then apply the formula or VBA code.
Thank you!
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello, Michael H.
Thanks for your kindly explanation.
Hope this can help others in the future.😄
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello Sir! The VBA worked wonders, thank you very much for that! I was wondering, If I change the original data, is it possible to refresh the column with the unique values automatically?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello Ioannis,

Glad to help. After you change the original data, the VBA can not refresh the result automatically. And the easiest way I can think of is to press Ctrl + Alt + F9 to refresh all results in worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
The array formula at the top is working great when used with data in the same sheet, however when I try to use it to reference the same exact data from another sheet the formula returns nothing. I'm unable to figure out why. Is there a limitation with array functions that prevents you from referencing ranges in a different sheet?

Thanks for any insight you can provide.
This comment was minimized by the moderator on the site
Hello Erin,

Glad to help. The INDIRECT function in this formula is more complicated to use when referencing data in other worksheets. It is not recommended to use this feature when referencing ranges in different worksheets.

For example: Now the data is in Sheet1, I want to reference the content of cell C2 of Sheet1 in Sheet2. First, in any two cells in Sheet2, such as D1 and D2, enter Sheet1 and C2, respectively. At this point, enter the formula in the empty cell of Sheet2:
=INDIRECT("'"&D1&"'!"&D2), then the content of cell C2 in Sheet1 can be returned.

As you can see, it make things way more complex. Hope my explanation can help. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Czy to żart?
This comment was minimized by the moderator on the site
can we create uniqdata function instead of macro?
This comment was minimized by the moderator on the site
Hi, İlhan,If you like a User Defined Function to create a formula for solving this problem, the below code may help you:After inserting the code, select a list of cells where you want to put the results. Then type this formula:=Uniques(A1:C4)  in the formula bar.Press Ctrl+Shift+Enter keys together. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
This comment was minimized by the moderator on the site
Thanks for the code. I'm using the VBA code of this page. Is there a way to add a sorting code after the unique values are extracted so it sorts it automatically?
This comment was minimized by the moderator on the site
Regarding the formula version, could you explain in more detail what this portion is doing? *100+COLUMN($A:$C),7^8)),"R0C00") Specifically, what are the *100, 7^8, and "R0C000" doing? I'm understanding everything else, but I can't figure out what these are for.
This comment was minimized by the moderator on the site
Little late for my response here but...
ROW($2:$9)*100 - this is multiplying the row number *100, so if it's in row 5, now the number is 500
COLUMN($A:$C) - this gets added to the row*100 number, so if it's row 5 col 2, then the number is 502.
7^8)), - this (I think) is to have a max value for the min statement from earlier.
"R0C00") - this formats the text based on the number. In the example, we had 502 so this gives R5C02 (row 5, col 02).

If you have a lot of columns but not many rows, then you could change it to ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
This comment was minimized by the moderator on the site
i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
This comment was minimized by the moderator on the site
Hello, Cody,
The above formula works well in my worksheet, could you give a screenshot of your data problem here?
Thank you!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations