Monthly Archives: September 2014

Uitgebreid Euclidisch Algoritme

Bijvoorbeeld in A1:
=ExtendedEuclideanWithSteps(1190;672;33;11;TRUE)
Function ExtendedEuclideanWithSteps(a As Long, b As Long, ByRef x As Long, ByRef y As Long, Optional showSteps As Boolean = False) As Long
    ' Extended Euclidean Algorithm with step-by-step explanation
    Dim temp As Long
    Dim x1 As Long, x2 As Long, y1 As Long, y2 As Long
    Dim q As Long, r As Long
    Dim stepCount As Integer
    Dim result As String
    Dim originalA As Long, originalB As Long
    
    ' Store original values
    originalA = a
    originalB = b
    
    ' Ensure positive numbers
    a = Abs(a)
    b = Abs(b)
    
    ' Handle zero cases
    If a = 0 And b = 0 Then
        x = 0: y = 0
        ExtendedEuclideanWithSteps = 0
        Exit Function
    End If
    
    If a = 0 Then
        x = 0: y = Sgn(b)
        ExtendedEuclideanWithSteps = b
        Exit Function
    End If
    
    If b = 0 Then
        x = Sgn(a): y = 0
        ExtendedEuclideanWithSteps = a
        Exit Function
    End If
    
    ' Initialize coefficients and step counter
    x1 = 1: x2 = 0
    y1 = 0: y2 = 1
    stepCount = 0
    
    If showSteps Then
        result = "Extended Euclidean Algorithm Steps:" & vbCrLf & vbCrLf
        result = result & "Find GCD(" & originalA & ", " & originalB & ") and coefficients x, y" & vbCrLf
        result = result & "such that: " & originalA & "x + " & originalB & "y = GCD" & vbCrLf & vbCrLf
        result = result & "Step " & stepCount & ":" & vbCrLf
        result = result & "a = " & a & ", b = " & b & vbCrLf
        result = result & "x1 = " & x1 & ", x2 = " & x2 & vbCrLf
        result = result & "y1 = " & y1 & ", y2 = " & y2 & vbCrLf & vbCrLf
    End If
    
    ' Extended Euclidean Algorithm
    Do While b <> 0
        stepCount = stepCount + 1
        q = a \ b
        r = a Mod b
        
        If showSteps Then
            result = result & "Step " & stepCount & ":" & vbCrLf
            result = result & a & " = " & b & " * " & q & " + " & r & vbCrLf
        End If
        
        ' Update coefficients
        temp = x2
        x2 = x1 - q * x2
        x1 = temp
        
        temp = y2
        y2 = y1 - q * y2
        y1 = temp
        
        If showSteps Then
            result = result & "New coefficients: x1 = " & x1 & ", x2 = " & x2 & vbCrLf
            result = result & "                 y1 = " & y1 & ", y2 = " & y2 & vbCrLf & vbCrLf
        End If
        
        ' Update a and b
        a = b
        b = r
    Loop
    
    ' Set results
    x = x1
    y = y1
    ExtendedEuclideanWithSteps = a
    
    If showSteps Then
        result = result & "Final Result:" & vbCrLf
        result = result & "GCD(" & originalA & ", " & originalB & ") = " & a & vbCrLf
        result = result & "Coefficients: x = " & x & ", y = " & y & vbCrLf
        result = result & "Verification: " & originalA & "*" & x & " + " & originalB & "*" & y & " = "
        result = result & (originalA * x + originalB * y) & " = " & a
        
        MsgBox result
    End If
End Function

Maak unieke lijst en sorteer van A-Z

In $A$3:$A$93 namen waaronder veel dubbele namen, We willen in C3 een lijst met unieke namen en die lijst sorteren van A naar Z. Niet al te moeilijk. We hebben slechts één formule nodig.

In [C3] =IFERROR(INDEX($A$3:$A$93; MATCH(SMALL(IF(COUNTIF($C$2:C2; $A$3:$A$93)=0; COUNTIF($A$3:$A$93; "<"&$A$3:$A$93); ""); 1); COUNTIF($A$3:$A$93; "<"&$A$3:$A$93); 0));"")

Invoeren met Ctrl+Shift+Enter (NIET alleen Enter)
En doorvoeren naar beneden.

Calculating Top-N and Adding Amounts

On Sheet1 the names of sellers are in Column C and in Column D are amounts. In Column C, the name of the same employee can appear multiple times.

On Sheet2, we want to show the seller with the highest total amount and then the second seller, etc. A kind of Top 10 so to speak. In addition, there is a criterion, we only count the amounts for which column E says “Sold”.

Sheet1

Sheet2

A1 Since you want to see a Top-N, enter the number here, for example 8. Then you get to see the Top-8.

You have to enter the formulas with Ctrl+Shift+Enter (not just Enter)

A2 =LARGE(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold");MIN(A1;SUM(IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);1))))

A3 =IFERROR(SUM(IF(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23))>=A2;1));0)

A5 =IF($B5="";"";INDEX(Sheet1!$C$2:$C$23;SMALL(IF(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold")=$B5;ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);COUNTIFS($B$5:B5;B5))))

B5 =IF(ROWS($B$5:B5)>$A$3;"";LARGE(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold");ROWS($B$5:B5)))

Verpakkingseenheid (doos) * item (fles) totaliseren

We hebben een tabel zoals in de afbeelding hieronder. Het doel is om dezelfde producten op te tellen en te vermenigvuldigen. Bijvoorbeeld: het aantal fusten “Rhönbräu Klosterbier” maal het aantal items (in dit geval liters, omdat de eenheid voor fusten liters is). In totaal hebben we 48 fusten “Rhönbräu Klosterbier”. Een deel van de fusten bevat 15 liter en een ander deel 12 liter. Dit komt samen uit op een totaal van 648 liter “Rhönbräu Klosterbier”.

Formule in G2 is:
=SUMPRODUCT(--(A$2:A$11=F2);B$2:B$11;D$2:D$11)
Doorvoeren naar beneden.

Gegevens in één cel splitsen

Gegevens in één cel splitsen en weergeven per cel. In Kolom A en B staan gegevens. Er staan telkens twee namen in één cel bijvoorbeeld in A2 . In B2 staan twee steden. Zie afbeelding.

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

Sub Splitsen()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a) * 2, 1 To 2)
  For i = 1 To UBound(a)
    c = Split(a(i, 1), Chr(10))
    b(i * 2 - 1, 1) = c(0)
    b(i * 2, 1) = c(1)
    c = Split(a(i, 2), Chr(10))
    b(i * 2 - 1, 2) = c(0)
    b(i * 2, 2) = c(1)
  Next
  Range("D2:E2").Resize(UBound(b)).Value = b
End Sub

8 Tekens vanaf de linkerkant van een tekenreeks verwijderen

Met behulp van VBA code 8 tekens vanaf de linkerkant van een tekenreeks verwijderen. Tekenreeksen staan in kolom A.

Je moet wel opletten want een cijfer als 02 kapt Excel af en toont dat als 2. Hierdoor wordt de tekenreeks “AFT5985602” weergegeven als 2. Er zijn in dat geval 9 cijfers weggelaten. Daarom moet je de 2 aan het einde van de code niet veranderen

Option Explicit

Sub Verwijder_8_Tekens()
  Range("A2", Range("A" & Rows.Count).End(xlUp)).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 1))
End Sub

Splits data en zet in één cel gescheiden door komma

In Kolom A namen van Employees waarbij dezelfde naam meerdere keren kan voorkomen. In Kolom B namen van Customers. Dezelfde Employee kan meerdere Customers hebben. Dat wil je weergeven vanaf cel A26. Zie afbeelding.

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

Function aconcat(a As Variant, Optional sep As String = "") As String
' © Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Plaats de volgende Formules:
A26=IFERROR(INDEX($A$2:$A$23;MATCH(0;INDEX(COUNTIF($A$25:A25;$A$2:$A$23)+(A$2:A$23="");0);0));"")
Invoeren met gewoon Enter en doorvoeren naar beneden.

B26 =IF(A26="";"";MID(aconcat(IF($A$2:$A$23=A26;", "&$B$2:$B$23;""));3;999))
Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+Enter, NIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }.

Let op: Plaats die accolades { } niet handmatig.
Doorvoeren naar beneden.