De onderstaande zogenaamde “User Defined function” oftewel een zelfgemaakte functie, splitst de gegevens die in 1 cel staan en meerdere regels omvat.

Opzet:
– Je gegevens staan in kolom A. In dit geval alleen in A1.
– Je krijgt dan als resultaat zoals je het op de afbeelding ziet.

1. Kopieer de onderstaande code
2. Open een nieuwe werkmap
3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
5. Druk op M om een standaard module in te voegen
6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel

Function Split_It_Up(rngBereik As Range, RijNummer)
    
    'Altijd herberekenen
    Application.Volatile
    
    a = InStr(1, rngBereik.Value, Chr(10), 1)
    b = InStr(a + 1, rngBereik.Value, Chr(10), 1)
    c = InStr(b + 1, rngBereik.Value, Chr(10), 1)
    d = InStr(c + 1, rngBereik.Value, Chr(10), 1)
    e = InStr(d + 1, rngBereik.Value, Chr(10), 1)
    If RijNummer = 1 Then Split_It_Up = Left(rngBereik.Value, a - 1)
    If RijNummer = 2 Then Split_It_Up = Mid(rngBereik.Value, a + 1, (b) - (a + 1))
    If RijNummer = 3 Then Split_It_Up = Mid(rngBereik.Value, b + 1, (c) - (b + 1))
    If RijNummer = 4 Then Split_It_Up = Mid(rngBereik.Value, c + 1, (d) - (c + 1))
    If RijNummer = 5 Then Split_It_Up = Mid(rngBereik.Value, d + 1, (e) - (d + 1))
    If RijNummer = 6 Then Split_It_Up = Right(rngBereik.Value, Len(rngBereik.Value) - e)
End Function

Zet de volgende formule in C1:
=Split_It_Up($A1;KOLOM(A1))
En naar rechts slepen (en eventueel naar beneden)

Alle bestanden in een bepaalde map weergeven op en werkblad.
Deze is eenvoudig

1. Kopieer de onderstaande code
2. Open een nieuwe werkmap
3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
5. Druk op M om een standaard module in te voegen
6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
8. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbeklik op de macro naam om te starten.

Sub Alle_Bestandsnamen_Weergeven()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet

'Verwijzing naar Scripting.FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Werkblad toevoegen
Set ws = Worksheets.Add

 'Verkrijg het map object dat geassocieerd is met de directory
Set objFolder = objFSO.GetFolder("C:\windows") '<<< Geef een directory op
ws.Cells(1, 1).Value = "De bestanden gevonden in " & objFolder.Name & " zijn:"

 'Doorloop de bestanden collectie
For Each objFile In objFolder.Files
    ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
Next

 'Opruimen!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing

End Sub

Een ander voorbeeld om de gegevens in een cel te splitsen en de losse items in naast gelegen cellen te plaatsen. Selecteer A1 (oranje cell)

1. Kopieer de onderstaande code
2. Open een nieuwe werkmap
3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
5. Druk op M om een standaard module in te voegen
6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
8. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbeklik op de macro naam om te starten.

Sub SplitsGegevens()
'Splits data in een cel naar naast gelegen cellen
Dim SplitsData As Variant
Dim intTotaal As Integer
    
    'Gegevens van een cel in matrix opslaan
    SplitsData = Split(ActiveCell.Value, Chr(10))
    
    'Bovengrens matrix bepalen
    intTotaal = UBound(SplitsData)
    
    'Naar naast gelegen cellen schrijven
    Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), _
    Cells(ActiveCell.Row, ActiveCell.Column + 1 + intTotaal)).Value = SplitsData
End Sub

Selecteer ergens een cel in het bereik A1:D49 en toets Ctrl+L om een tabel te creëren met de naam Table1.
Of toets Ctrl+Shift+F3, Create names for values in the top row (Top Row aanvinken)

[H2] >>> Data Validation | Data Validation | List | Source:=$F$4:$F$21
[H2] is weliswaar geen combobox maar je kunt wel waarden kiezen. Als je geen waarden ziet, moet je even naar boven scrollen.

[D2]=SUM(IF(COUNTIF(A$2:A2;A2)<>1;0;COUNTIF(A$2:A2;A2));D1)
(Doorvoeren naar beneden)

[F2]=MAX(Table1[Dummy])

[F4]=IF(ROWS(F$4:F4)>$F$2;"";INDEX(Table1[Steden];MATCH(ROWS(F$4:F4);Table1[Dummy];0)))
(Doorvoeren naar beneden)

[I2]=COUNTIF(Table1[Steden];$H$2)

Onderstaande formules zijn Matrixformules. Invoeren met Ctrl+Shift+Enter
en vervolgens (Doorvoeren naar beneden)

[K2]=IF(ROWS(K$2:K2)>$I$2;"";INDEX(Sheet1!A$2:A$49;SMALL(IF(Table1[Steden]=Sheet1!$H$2;ROW(Table1[Steden])-ROW(Sheet1!$I$2)+1);ROWS(K$2:K2))))

[L2]=IF(ROWS(L$2:L2)>$I$2;"";INDEX(Sheet1!C$2:C$49;SMALL(IF(Table1[Steden]=Sheet1!$H$2;ROW(Table1[Steden])-ROW(Sheet1!$I$2)+1);ROWS(L$2:L2))))

Klein Excel bestand dat je kunt gebruiken voor dit voorbeeld

Selecteer ergens een cel in het bereik A1:C4000 en toets Ctrl+L (of Ctrl+L) om een tabel te creëren met de naam Table1. Nu kun je gemakkelijk naar die Table1 verwijzen in de onderstaande formules.

[Z2:Z444] een lijst met plaatsnamen van Nederland. 
[E2] >>> Gegevensvalidatie | Gegevensvalidatie | Lijst | Bron:=$Z$2:$Z$444

[F2]=COUNTIF(Table1[Steden];E2)
[E4]=IF(ROWS(E$4:E4)<=F$2;$E$2;"") (Doorvoeren naar beneden)

Onderstaande formules zijn Matrixformules. Invoeren met Ctrl+Shift+Enter:
Er verschijnen dan accolades { } om de formule. Niet zelf typen (want dan werkt de formule niet) en vervolgens (Doorvoeren naar beneden)

[F4] =IF(ROWS(F$4:F4)<=$F$2;INDEX(Table1[Provincie];SMALL(IF(Table1[Steden]=E$2;ROW(Table1[Steden]));ROWS(F$4:F4))-1);"")

[G4] =IF(ROWS(G$4:G4)<=$F$2;INDEX(Table1[PostCode];SMALL(IF(Table1[Steden]=E$2;ROW(Table1[PostCode]));ROWS(G$4:G4))-1);"")

Stel, je gegevens staan van links naar rechts, Oftewel horizontaal. Door middel van een dropdown en wat formules kun je specifieke gegevens ophalen en verticaal weergeven.

[A9] = Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan: Lijst | Bron: =$A$2:$A$7

[A10]=IFERROR(INDEX(Sheet1!$B$1:$J$1;SMALL(IF(1-(INDEX(Sheet1!$B$2:$J$7;MATCH($A$9;Sheet1!$A$2:$A$7;0);0)=""); COLUMN(Sheet1!$B$1:$J$1)-COLUMN(Sheet1!$B$1)+1);ROWS(A10:A$10)));"")

[B10]=IFERROR(INDEX(Sheet1!$B$2:$J$7;MATCH($A$9;Sheet1!$A$2:$A$7;0);SMALL(IF(1-(INDEX(Sheet1!$B$2:$J$7;MATCH($A$9;Sheet1!$A$2:$A$7;0);0)="");COLUMN(Sheet1!$B$1:$J$1)-COLUMN(Sheet1!$B$1)+1);ROWS(A$10:A10)));"")

Beide formules invoeren met Ctrl+Shift+Enter

Twee kolommen doorzoeken op dubbele waarden en op NIET dubbele waarden.
Gegevens staan in kolom A en kolom B. In kolom C, D en E staan de resultaten.

Dit kan met de volgende formules

Formule voor NIET dubbele waarden:

=IFERROR(INDEX($B$2:$B$20;SMALL(IF(1-ISNUMBER(MATCH($B$2:$B$20;$A$2:$A$20;0)); ROW($B$2:$B$20)-ROW($B$2)+1);ROWS($C$2:C2)));"")

Let op ! ! ! Deze formule invoeren met Ctrl+Shift+Enter, dus NIET gewoon Enter. Vervolgens doorvoeren naar beneden.

Formule voor dubbele waarden:
Deze formule invoeren met Enter. Vervolgens doorvoeren naar beneden.

=IF(ISERROR(MATCH(A2;$B$2:$B$20;0));"";A2)

Formule voor NIET dubbele waarden EN de POSITIE:
Deze formule invoeren met Enter. Vervolgens doorvoeren naar beneden.

=IF(ISERROR(MATCH(A2;$B$2:$B$20;0));B2;"")

Gegevens staan in kolom A. In kolom B staan de resultaten.

Kan met de volgende formule:
Formule voor unieke waarden:

=IFERROR(INDEX(A$2:A$20;MATCH(SMALL(IF(A$2:A$20<>"";IF(
COUNTIF(A$2:A$20;A$2:A$20)=1;A$2:A$20));ROWS(B$2:B2));A$2:A$20;0));"")

Let op ! ! ! Formule invoeren met Ctrl+Shift+Enter dus niet alleen met Enter.

De opdracht is simpel. Dubbele waarden in het bereik A2:L8 opsporen en weergeven in kolom N.

Formule in N2.

=ALS.FOUT(KLEINSTE(ALS($A$2:$L$8<>"";ALS(AANTAL.ALS($A$2:$L$8;$A$2:$L$8)>1;$A$2:$L$8));
SOM(AANTAL.ALS($A$2:$L$8;N$1:N1))+1);"")

Invoeren met Ctrl+Shift+Enter. Niet alleen met Enter. Indien je dit correct doet, plaatst Excel accolades om de formule { }
Let op ! ! ! Plaats die accolades niet zelf.

Vervolgens doorvoeren tot N8

Gegevens in bereik: A5:F96

Keuzelijst in A2: Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan: Lijst | Bron: =$A$6:$A$96

Formule in B2:

=IFERROR(INDEX(B$6:B$96;SMALL(IF(ISNUMBER(MATCH($A$6:$A$96;$A$2;0));ROW($A$6:$A$96)-ROW($A$6)+1);ROWS($A$6:B6)));"")

Invoeren met Ctrl+Shift+Enter. Niet alleen met Enter. Indien je dit correct doet, plaatst Excel accolades om de formule { }
Let op ! ! ! Plaats die accolades niet zelf.

Vervolgens doorvoeren tot F2

Deze formule is ook mogelijk

=INDEX($B$6:$F$96;MATCH($A2;$A$6:$A$96;0);COLUMNS($B$2:B2))