Geplaatst op 1 juni 2014

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.

Geplaatst op 1 juni 2014

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

Kan met de volgende formule:
Formule voor unieke waarden:

=ALS.FOUT(INDEX(A$2:A$20;VERGELIJKEN(KLEINSTE(ALS(A$2:A$20<>””;ALS(
AANTAL.ALS(A$2:A$20;A$2:A$20)=1;A$2:A$20));RIJEN(B$2:B2));A$2:A$20;0));””)

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

Geplaatst op 8 juni 2014

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

Geplaatst op 8 juni 2014

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))

Geplaatst op 17 juni 2014

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

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

Geplaatst op 17 juni 2014

Dit keer een korte.

Aantal waarden tellen die in één cel staan en gescheiden zijn door komma.
1,2,56,89,34,90,23,14,67

=LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;",";""))+1

Antwoord = 9

Geplaatst op 17 juni 2014

Soms staan je gegevens op een ongelukkige manier gerangschikt in slechts één kolom. Dus vertikaal. Er zit wél regelmaat in. Bijvoorbeeld telkens 6 rijen gegevens die bij elkaar horen. Je kunt die gegevens eenvoudig verdelen zodat ze in rijen (horizontaal) komen te staan.

Zie afbeeldingen ter illustratie.
VOOR:

NA:

De volgende formule in C2 plaatsen en vervolgens naar rechts en naar beneden slepen.

=ALS.FOUT(INDEX($A$2:$A$31;(RIJEN(C$2:C2)-1)*6+REST(KOLOMMEN($C2:C2)-1;6)+1);””)

Geplaatst op 6 juli 2014

Hoe haal je het numerieke gedeelte op uit een alfanumerieke tekenreeks?
Bijvoorbeeld: als cel A3 de tekenreeks esd K+ WS4600 bevat, moet de waarde 4600 worden geretourneerd in cel B3 of in cel C3. Hiervoor hebben we 2 functies: strCountNumbers (in B3) en GetNumbers (in C3)

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. Formule invoegen in juiste cel.

Bijvoorbeeld in B3: =strCountNumbers(A3) of =GetNumbers(A3)

Function strCountNumbers(strText As String) As String
Dim intLengte As Integer, strChar As String, X As Integer, strNumber As String
    intLengte = Len(strText)
    For X = 1 To intLengte
        strChar = Mid(strText, X, 1)
        If strChar Like "[0-9]" Then
            strNumber = strNumber & strChar
        End If
    Next
    strCountNumbers = strNumber
End Function

Function strCountNumbers(strText As String) As String
Dim intLengte As Integer, strChar As String, X As Integer, strNumber As String
    intLengte = Len(strText)
    For X = 1 To intLengte
        strChar = Mid(strText, X, 1)
        If strChar Like "[0-9]" Then
            strNumber = strNumber & strChar
        End If
    Next
    strCountNumbers = strNumber
End Function

Function GetNumbers(ByVal strText As String) As String
  Dim X As Long
  For X = 1 To Len(strText)
    If Mid(strText, X, 1) Like "[!0-9]" Then Mid(strText, X) = " "
  Next
  GetNumbers = Replace(strText, " ", "")
End Function

Function GetNumbers(ByVal strText As String) As String
  Dim X As Long
  For X = 1 To Len(strText)
    If Mid(strText, X, 1) Like "[!0-9]" Then Mid(strText, X) = " "
  Next
  GetNumbers = Replace(strText, " ", "")
End Function

Getallen uit het BEGIN van een tekenreeks halen.

Formule in B3 invoeren

=LINKS(A3;VERGELIJKEN(WAAR;ISFOUT(1*LINKS(SUBSTITUEREN(ALS(ISGETAL
(-DEEL(A3;VIND.ALLES(“.”;A3&”.”)+1;1));A3;SUBSTITUEREN(A3;”.”;”X”))
;” “;”X”)&”X”;RIJ(INDEX(A:A;1):INDEX(A:A;99))));0)-1)

Nog een bruikbare formule:
Formule in B3 invoeren

=LINKS(A3; VERGELIJKEN(WAAR; DEEL
(A3 & “x”; {1\2\3\4\5\6\7\8\9\10\11\12}; 1) > “9”; 0) – 1)

Zie ook: Garbage data, cijfers eruit halen.

Geplaatst op 6 juli 2014

Voornaam, achternaam, alias, tussenvoegsel en/of komma staan niet in goede volgorde. Dat moet toch te herstellen zijn.

Formule in B2 en eventueel naar beneden kopiëren.

=DEEL(VERVANGEN(A2;VIND.ALLES(” (“;A2&” (“);0;” “&LINKS
(A2;VIND.ALLES(“,”;A2)-1));VIND.ALLES(“,”;A2)+2;99)

Geplaatst op 6 juli 2014

In een jaaroverzicht staan in de bovenste rij de namen van de maand. Daaronder bijvoorbeeld wat verkoopcijfers. Staan er dubbele waarden in dat bereik?

Formule in N2 en naar beneden kopiëren.

Formule 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.

=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);””)