Data van kolommen naar rijen

Uncategorized

Een klant heeft meerdere produkten besteld. Het ID van de klant staat in kolom A. Merk op dat dezelfde ID meerdere keren voorkomt.  De produkten staan in kolom B. In kolom C zet je de unieke ID’s.

Formule in D2:

[D2] =IFERROR(INDEX($B$2:$B$13;SMALL(IF($A$2:$A$13=$C2;ROW(A$2:A$13)-ROW(A$2)+1);COLUMNS($D$2:D2)));””)

Invoeren met Ctrl+Shift+Enter
Vervolgens doorvoeren naar rechts en naar beneden.

Prijsverloop tonen

Uncategorized

[F3]=SUM(IF(A3:A27=F2;IF(ISNUMBER(D3:D27);1)))

[D3]=IF(ISNA(MATCH(C3;$D$2:D2;0));C3;IF(ISNA(MATCH(C3;$D$2:D2;0))*ISNA(MATCH($A3&”|”&$B3;$A$2:A2&”|”&$B$2:B2;0));C3;IF(ISNUMBER(LOOKUP(9,99999999999999E+307;1/($A$2:A2=A3);$C$2:C2));IF(LOOKUP(9,99999999999999E+307;1/($A$2:A2=A3);$C$2:C2)<>C3;C3;”#”);”#”)))

[E5]=IF(ROWS($E$3:E3)<=$F$3;INDEX($B$3:$B$27;SMALL(IF($A$3:A27=$F$2;IF(ISNUMBER($D$3:$D$27);ROW($B$3:$B$27)-ROW($B$3)+1));ROWS($E$3:E3)));””)

[F5]=IF($E5=””;””;INDEX($D$3:$D$27;SMALL(IF($A$3:$A$27=$F$2;IF($B$3:$B$27=$E5;ROW($D$3:$D$27)-ROW($D$3)+1));COUNTIFS($E$5:E5;E5))))

Let op: Alle formules invoeren met Ctrl+Shift+Enter.

[D3], [E5], [F5] doorvoeren naar beneden.

Zoeken, dubbele waarden in Kolom B

Uncategorized

Je wilt een item opzoeken maar het probleem is dat er dubbele waarden in kolom B staan. Als je VLOOKUP gebruikt, wordt alleen de 1-ste overeenkomst gevonden. Maar wat als je de 2-de of 3-de overeenkomst wil vinden. De oplossing is dat je een extra kolom toevoegt (Kolom A) en de dubbele items, Kolom B, van een volgnummer voorziet.

Het volgnummer kun je op de volgende manier genereren:
We gaan er van uit dat er maximaal 3 dubbele waarden voorkomen. In het gele gedeelte, Kolom H, vul je de parameters in:
[H1] Type je de waarde die je wilt zoeken. Bijvoorbeeld: ITEM 3
[H2] laat je leeg
[H3] zet je, 1
[H4] zet je, 2
[H5] zet je, 3

In A2 zet je de formule om de volgnummers te genereren.
[A2] =IF(COUNTIF($B$2:$B$14;$H$1)>1;IF(B2=$H$1;COUNT($A$1:A1)+1;””);””)
Vervolgens doorvoeren naar beneden tot A14

Tenslotte,
[H8] =H2
[H9] =IFERROR(INDEX($C$2:$C$14;MATCH(H$2&$H1;MATCH($A$2:$A$14&$B$2:$B$14;);0));””)
Formule doorvoeren naar beneden tot H12.

De truc is eigenlijk dat je het volgnummer en het item samenvoegt tot één zoekwaarde d.m.v. het Ampersand teken. Dat gebeurt in het rood gemerkte gedeelte:
H$2&$H1 wordt dus in feite 1ITEM 3. Hetzelfde gebeurt met de waarden in de kolommen A en B: $A$2:$A$14&$B$2:$B$14

Cijfers van bepaalde maanden opzoeken

Uncategorized

Stel, je hebt verkoopcijfers of iets dergelijks en je wilt bepaalde maanden van een bepaalde verkoper optellen. In dit voorbeeld de maanden Februari en Mei. Tabel staat hier onder.
Let eerst op kolom I. In I1 zet je Id, en in I3 Totaal. In I2 kun je de id van de verkoper typen maar handiger is om daar een keuzelijst van te maken. Als je unieke waarden hebt in kolom A is dat een fluitje van een cent. Kies voor:

Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan > Lijst | Bron > selecteer de waarden in kolom A

Nu kun je gewoon een waarde in I2 kiezen middels de keuzelijst.

Formule gaat in cel I4:

=SOM(SOMMEN.ALS(INDEX($B$2:$G$10;VERGELIJKEN($I$2;$A$2:$A$10;0);0);$B$1:$G$1;{“februari”\”Mei”}))

Let even speciaal op het rode gedeelte in de formule. Daar geef je de maanden op die je wilt tellen, gescheiden door een backslash.Let ook op de Accolades { }

Gegevens ophalen met ADO

Uncategorized

ADO betekent “ActiveX Data Objects“. Kun je verder vergeten. Wat kun je er mee? Gegevens ophalen uit een ander (Excel)bestand. Dat andere bestand fungeert dan als een soort database. Wat mij betreft kun je net zo goed het bestand openen en de gegevens die je nodig hebt filteren en vervolgens kopiëren om er verder mee te werken.

Excel bestand is tabellen.xlsx en de data staat op het blad shippers. Zie onderstaand screenshot. Dat bestand kun je zelf aanmaken en dan openen in Excel om het te kunnen lezen.

Wie persé die voorziening wil gebruiken hier twee voorbeelden.
Onderstaande code kopieren en plakken in blanco werkmap. Zorg dat de blanco werkmap op de voorgrond staat en dan de code laten uitvoeren.

VOORBEELD 1

Option Explicit

'***********************************************
'Set reference to:
'Microsoft ActiveX Data Objects Library
'Via: Alt+F11 | Extra | References
'***********************************************

Sub Voorbeeld_ADO_Verbinding_Voorbeeld_1()
    Dim objVerbinding As New ADODB.Connection
    Dim objGegevensSet As New ADODB.Recordset
    Dim strPad As String, strVerbind As String, strSQL As String
    
    'Path to file
    strPad = "C:\temp\tabellen.xlsx"
    
    strVerbind = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strPad & ";HDR=Yes';"
    
    objVerbinding.Open strVerbind
    
    'Tablename is de sheetname in this example [shippers$]
    'Dollarsign is mandatory behind shippers.
    strSQL = "SELECT * From [shippers$]"
        
    objGegevensSet.Open strSQL, objVerbinding
    
    'Paste data in activesheet
    ActiveSheet.Range("A2").CopyFromRecordset objGegevensSet
    
    'Close dataset
    objGegevensSet.Close
    
    'Close connection
    objVerbinding.Close
End Sub

VOORBEELD 2

Option Explicit

'***********************************************
'Set reference to:
'Microsoft ActiveX Data Objects Library
'Via: Alt+F11 | Extra | References
'***********************************************

Sub Voorbeeld_ADO_Verbinding_Voorbeeld_2()
    Dim arrMatrix As Variant
    Dim objVerbinding As New ADODB.Connection
    Dim objGegevensSet As New ADODB.Recordset
    Dim strPad As String, strVerbind As String, strSQL As String
    Dim i As Long, j As Long
    
    'Path to file
    strPad = "C:\temp\tabellen.xlsx"
    
    strVerbind = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strPad & ";HDR=Yes';"
    
    objVerbinding.Open strVerbind
    
    'Tablename is de sheetname in this example [shippers$]
    'Dollarsign is mandatory behind shippers.
    strSQL = "SELECT * From [shippers$]"
        
    objGegevensSet.Open strSQL, objVerbinding
    
    'Load data in Array
    arrMatrix = objGegevensSet.GetRows
    
    'Paste data from array to activesheet
    For i = 0 To UBound(arrMatrix, 2) 'Rows
        For j = 0 To UBound(arrMatrix, 1) 'Columns
            ActiveSheet.Cells(i + 2, j + 1).Value = arrMatrix(j, i)
        Next j
    Next i
    
    'Close dataset
    objGegevensSet.Close
    
    'Close connection
    objVerbinding.Close
End Sub

Unieke waarden tonen, 1 criterium, totaal

Uncategorized

Verkopers hebben goed hun best gedaan en van alles verkocht en geld verdiend. Nu wil je de totalen hebben van die verkopers maar slechts als ze voldoen aan een bepaald criterium. Bijvoorbeeld: Periode = 3.

In de kolom Verkopers, Kolom B, staan de namen. Er staan echter dubbele waarden in en je wilt de naam van de verkoper slechts één keer weergeven en daarachter het totaal van periode = 3. Kijk naar de opzet van het werkblad. Resultaten weergegeven vanaf E5.

FORMULES:

In [F2] voer je handmatig de periode in.

Invoeren met Ctrl+Shift+Enter, NIET met Enter.
[F3] =SUM(IF(FREQUENCY(IF(Periode=$F$2;IF(Verkoper<>””;MATCH(Verkoper;Verkoper;0)));intRij);1))

Invoeren met Ctrl+Shift+Enter, NIET met Enter
[E5] =IF(ROWS($E$5:E5<=$F$3;INDEX(Verkoper;SMALL(IF(FREQUENCY(IF(Periode=$F$2;IF(Verkoper<>””;
MATCH(Verkoper;Verkoper;0)));intRij);intRij);ROWS($E$5:E5)));””)

Invoeren met Enter
[F5]=IF(E5<>””;COUNTIFS(Periode;$F$2;Verkoper;E5);””)

Invoeren met Enter
[G5]=IF($E5=””;””;SUMIFS(Bedrag;Verkoper;$E5;Periode;$F$2))

Namen geven: Ga naar Formules | in de groep Gedefinieerde namen |  Namen beheren

Periode
=Sheet1!$A$2:$A$40

Verkoper
=Sheet1!$B$2:$B$40

Bedrag
=Sheet1!$C$2:$C$40

intRij
=ROW(Verkoper)-ROW(INDEX(Verkoper;1;1))+1

Twee lijsten vergelijken

Uncategorized

Je hebt een controlelijst met Id, Naam en Email. Nu krijg je een nieuwe lijst met BIJNA dezelfde gegevens echter het Id ontbreekt. Als Naam en Email hetzelfde zijn kun je het Id zoeken in de controle lijst. De truc is om de Naam en het Email samen te voegen met het & teken en vervolgens te vergelijken met de Naam en het Email in de andere lijst (die je natuurlijk ook samenvoegt).

Formule invoeren met Ctrl+Shift+Enter. Niet alleen Enter.

[E2]=IFERROR(INDEX($A$2:$A$22;MATCH(F2&G2;$B$2:$B$22&$C$2:$C$22;0));””)

Kopiëren naar beneden.