Nog een keer data in één cel splitsen

News around the world

Geplaatst op

Een ander voorbeeld om de gegevens in een cel te splitsen en de losse items in naast gelegen cellen te plaatsen.

Zie ook:
Tekst terugloop in cel splitsen naar meerdere cellen
Wederom tekst terugloop in cel splitsen
 

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

Gegevens ophalen middels dynamische keuzelijst (Deel 1)

News around the world

Geplaatst op

Screenshot layout:

Selecteer ergens een cel in het bereik A1:C27 en toets Ctrl+L om een tabel te creëren met de naam Tabel1.
Maak een Dummy kolom [D]
[H2] >>> Gegevensvalidatie | Gegevensvalidatie | Lijst | Bron:=$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]=SOM(ALS(AANTAL.ALS(A$2:A2;A2)<>1;0;AANTAL.ALS(A$2:A2;A2));D1) (Doorvoeren naar beneden)
[F2]=MAX(Tabel1[Dummy])
[F4]=ALS(RIJEN(F$4:F4)>$F$2;””;INDEX(Tabel1[Steden];
VERGELIJKEN(RIJEN(F$4:F4);Tabel1[Dummy];0))) (Doorvoeren naar beneden)
[I2]=AANTAL.ALS(Tabel1[Steden];$H$2)

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

[K2]=ALS(RIJEN(K$2:K2)>$I$2;””;INDEX(Blad1!A$2:A$27;
KLEINSTE(ALS(Tabel1[Steden]=Blad1!$H$2;RIJ(Tabel1[Steden])-RIJ(Blad1!$I$2)+1);RIJEN(K$2:K2))))

[L2]=ALS(RIJEN(L$2:L2)>$I$2;””;INDEX(Blad1!C$2:C$27;
KLEINSTE(ALS(Tabel1[Steden]=Blad1!$H$2;RIJ(Tabel1[Steden])-RIJ(Blad1!$I$2)+1);RIJEN(L$2:L2))))

Gegevens ophalen middels dynamische keuzelijst (Deel 2)

News around the world

Geplaatst op

Hier is een lijst met 3 kolommen die je kunt gebruiken voor dit voorbeeld

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

Tabel1[Steden] of Tabel1[Provincie] of Tabel1[Postcode]

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

[F2]=AANTAL.ALS(Tabel1[Steden];E2)
[E4]=ALS(RIJEN(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]{=ALS(RIJEN(F$4:F4)<=$F$2;INDEX(Tabel1[Provincie];KLEINSTE(ALS(Tabel1[Steden]=E$2;
RIJ(Tabel1[Steden]));RIJEN(F$4:F4))-1);””)}
[G4]{=ALS(RIJEN(G$4:G4)<=$F$2;INDEX(Tabel1[Postcode];KLEINSTE(ALS(Tabel1[Steden]=E$2;
RIJ(Tabel1[Postcode]));RIJEN(G$4:G4))-1);””)}

Gegevens ophalen en transponeren

News around the world

Geplaatst op

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]=ALS.FOUT(INDEX(Blad1!$B$1:$J$1;KLEINSTE(ALS(1-(INDEX(Blad1!$B$2:$J$7;
VERGELIJKEN($A$9;Blad1!$A$2:$A$7;0);0)=””); KOLOM(Blad1!$B$1:$J$1)
-KOLOM(Blad1!$B$1)+1);RIJEN(A10:A$10)));””)
[B10]=ALS.FOUT(INDEX(Blad1!$B$2:$J$7;VERGELIJKEN($A$9;Blad1!$A$2:$A$7;0);
KLEINSTE(ALS(1-(INDEX(Blad1!$B$2:$J$7;VERGELIJKEN($A$9;Blad1!$A$2:$A$7;0);0)=””);
KOLOM(Blad1!$B$1:$J$1)-KOLOM(Blad1!$B$1)+1);RIJEN(A$10:A10)));””)

Unieke waarden vinden in 2 kolommen

News around the world

Geplaatst op

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:
=ALS.FOUT(INDEX($B$2:$B$20;KLEINSTE(ALS(1-ISGETAL(VERGELIJKEN($B$2:$B$20;$A$2:$A$20;0)); RIJ($B$2:$B$20)-RIJ($B$2)+1);RIJEN($C$2:C2)));””)

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

Formule voor dubbele waarden:

=ALS(ISFOUT(VERGELIJKEN(A2;$B$2:$B$20;0));””;A2)
Deze formule invoeren met Enter. Vervolgens doorvoeren naar beneden.

Formule voor NIET dubbele waarden:

=ALS(ISFOUT(VERGELIJKEN(A2;$B$2:$B$20;0));B2;””)
Deze formule invoeren met Enter. Vervolgens doorvoeren naar beneden.

Dubbele waarden (alweer ! ! !)

News around the world

Geplaatst op

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 ophalen d.m.v. keuzelijst

News around the world

Geplaatst op

Gegevens in bereik: A5:F96

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

Formule in B2:

=ALS.FOUT(INDEX(B$6:B$96;KLEINSTE(ALS(ISGETAL(VERGELIJKEN($A$6:$A$96;$A$2;0));RIJ($A$6:$A$96)-RIJ($A$6)+1);RIJEN($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))

Datumbereik splitsen in 1 dag per rij

News around the world

Geplaatst op

Je gegevens staan in een rij en de twee velden, Start en Stop, bevatten een datumbereik. Nu wil je voor elke dag in het datumbereik een nieuwe rij creëren.

Zie het voorbeeld in de afbeelding.

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.

Option Explicit
Sub Een_Dag_Per_Rij()
Dim varData As Variant, varSplitData As Variant
Dim lngRijen As Long, lngRijTotaal As Long, x As Long
Dim y As Long, lngRij As Long, lngRijMax As Long

varData = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
lngRijen = UBound(varData, 1)

For lngRij = 1 To lngRijen
    varData(lngRij, 6) = varData(lngRij, 5) - varData(lngRij, 4) + 1
    lngRijMax = lngRijMax + varData(lngRij, 6)
Next lngRij

If lngRijMax < Rows.Count Then
    ReDim varSplitData(1 To lngRijMax, 1 To 4)
    lngRijTotaal = 1
    
    For lngRij = 1 To lngRijen
        
        For x = 0 To varData(lngRij, 6) - 1
            
            For y = 1 To 3
                varSplitData(lngRijTotaal + x, y) = varData(lngRij, y)
            Next y
            varSplitData(lngRijTotaal + x, 4) = varData(lngRij, 4) + x
        
        Next x
        lngRijTotaal = lngRijTotaal + varData(lngRij, 6)
    
    Next lngRij
    
    Range("G2").Resize(lngRijMax, 4).Value = varSplitData
    Range("G1:J1").Value = Array("Id", "Naam", "Afwezigheidscode", "Datum")
Else
    MsgBox "Te veel rijen"
End If
End Sub