Van lijst naar matrix en terug

English version below.

Tabellen maken met tekst opgelost.
Bij het omzetten van eenvoudige lijsten naar matrices via draaitabellen liep ik tegen het probleem aan dat de draaitabellen uitgaan van hoeveelheden en mijn tekst waarden omzetten naar een aantal. Daarom was ik op zoek naar een alternatief voor draaitabellen waarbij de tekst gegevens in de matrix komen te staan in plaats van de aantallen.

Bij het zoeken zag ik dat anderen ook met dit probleem kampten en dat er geen bruikbare antwoorden bij zaten. Op de Microsoft site staat een heel eenvoudig macrootje dat alleen met enkele vaste waarden werkt. Ik heb dat macrootje als basis gebruikt om van een lijst met 3 kolommen een matrix te maken.

Al een hele tijd geleden had ik ook een macrootje gevonden, en aangepast waarmee je een matrix kunt omzetten naar een lijst met 3 kolommen. Dat staat iets verder beneden.

English version

Solved converting a list to a matrix
For some time I was looking to solve a problem with pivot-tables. When I convert a list to a pivot table the resulting text data is converted to a summary or a count. In my situation most of the fields either showed zero's or one's instead of the text. When I searched the web, I realised that I wasn't the only one with this problem and no one seemed to have a solution. That is why I used a very rudimentary example from a microsoft site and made it more flexible for me. It takes a 3 column list and converst it to a two dimentional matrix. The beauty is that here my text data remains as it was. So this is an alternative to the pivot-tables if you want to keep your text fields untouched. A long time ago I also found a VBA macro to convert a matrix in a table. I can't remember where I found it, so I can't give attributes here. I modified that Macro and included it here as well.

Matrix naar lijst (Matrix to list conversion)

Hier begin ik met een matrix met de opleidingen aan de bovenkant en de medewerkers aan de linker kant.
Het macrootje maakt hier de lijst met 3 kolommen van.

Here I start with a matrix with courses on top and employees on the left. The macro creates a 3 column list.



















Lijst naar Matrix (List to matrix conversion)

Hier maak ik van een lijst met 3 kolommen een matrix. De gegevens in 3 kolommen MOETEN beginnen bij vakje A2, B2 en C2. en het resultaat komt rechts van kolom E.

Here in convert the 3 column list to a matrix.

The data in the columns has to start at cells A2, B2 and C2. The resulting matrix is built to the right of column E.





Code to Convert a matrix to a 3 column list and back.
Nederlandse gebruikers moeten waarschijnlijk de comma's (,) veranderen in punt-comma (;).

Sub BuildTable()
'Cell A1 = Left Labels, Start with your data in Cell A2
'Cell B1 = Top Labels, Start with your data in Cell B2
'Cell C1 = Values, Start with your data in Cell C2
'Table starts in Cell F1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim ListRow, TableRow, TableColumn As Integer
Dim TableEntry As String
Dim CellToFill As Range
'Prepare the X and Y-axes
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
MYLastRowAddress = Range("F65536").End(xlUp).Address
ActiveSheet.Range("$F$1:" & MYLastRowAddress).RemoveDuplicates Columns:=1, Header:=xlNo
Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("G1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
MYLastRowAddress = Range("F65536").End(xlUp).Address
Application.CutCopyMode = False
ActiveSheet.Range("$F$2:" & MYLastRowAddress).RemoveDuplicates Columns:=1, Header:=xlNo
MYLastColAddress = Range("G1").End(xlToRight).Address
Range("F2").Select
'Fill in the values
ListRow = 2
Do Until Cells(ListRow, 1).Value = ""
' Get table entry from third column of list.
TableEntry = Cells(ListRow, 3).Value
' Get position of product name within range of row titles.
TableRow = Application.Match(Cells(ListRow, 1), _
Range("F2:" & MYLastRowAddress), 0)
' Get position of product size within range of column titles.
TableColumn = Application.Match(Cells(ListRow, 2), _
Range("G1:" & MYLastColAddress), 0)
Set CellToFill = Range("F1").Offset(TableRow, TableColumn)
' If there's already an entry in the cell,
' separate it from the new entry with a comma and space.
If CellToFill.Value <> "" Then CellToFill.Value = _
CellToFill.Value & ","
' Add the new entry to the cell.
CellToFill.Value = CellToFill.Value & TableEntry
ListRow = ListRow + 1
Loop
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub ReversePivotTable()
' The output table will have three columns.
Dim SummaryTable As Range, OutputRange As Range
Dim OutRow As Long
Dim r As Long, c As Long

On Error Resume Next
Set SummaryTable = ActiveCell.CurrentRegion
If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
MsgBox "Select a cell within the Matrix.", vbCritical
Exit Sub
End If
SummaryTable.Select
Set OutputRange = Application.InputBox(prompt:="Select a SINGLE cell for the 3-column output", Type:=8)
' Convert the range
OutRow = 2
Application.ScreenUpdating = False
OutputRange.Range("A1:C3") = Array("Left Labels", "Top Labels", "Values")
For r = 2 To SummaryTable.Rows.Count
For c = 2 To SummaryTable.Columns.Count
OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
OutRow = OutRow + 1
Next c
Next r
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Here is an example on how to create a table with 3 values for each entry (see picture below)

Sub BuildTripleTable()
'Cell A1 = Left Labels, Start with your data in Cell A2
'Cell B1 = Top Labels, Start with your data in Cell B2
'Cell C1 = Values1, Start with your data in Cell C2
'Cell D1 = Values2, Start with the data in Cell D2
'Cell E1 = Values3, Start with the data in Cell D2
'Table starts in row 1 of "MatrixColumn"
TopLabels = "B2"
LeftLabels = "A2"
FirstValue = "C2"
SecondValue = "D2"
MatrixColumn = "H"
MatrixHeader = "I" 'Select ONE column to the right of the MatrixColumn
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim ListRow, TableRow, TableColumn As Integer
Dim TableEntry As String
Dim CellToFill As Range
'Prepare the X-ax
Range(TopLabels).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range(MatrixColumn & "1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
MYLastRowAddress = Range(MatrixColumn & "65536").End(xlUp).Address
ActiveSheet.Range(MatrixColumn & "1:" & MYLastRowAddress).RemoveDuplicates Columns:=1, Header:=xlNo
Range(MatrixColumn & "1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range(MatrixHeader & "1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range(MatrixColumn & "1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
'Prepare the Y-ax
Range(LeftLabels).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range(MatrixColumn & "2").Select
ActiveSheet.Paste
'Left Values have been Pasted.
'Remove Duplicates
MYLastRowAddress = Range(MatrixColumn & "65536").End(xlUp).Address
Application.CutCopyMode = False
ActiveSheet.Range(MatrixColumn & "2:" & MYLastRowAddress).RemoveDuplicates Columns:=1, Header:=xlNo
'Duplicate the Y-ax the First time and mark the duplicated entries with -1
Range(MatrixColumn & "2").Select
LeftColumnSourceRange = MatrixColumn & "2:" & Range(Selection, Selection.End(xlDown)).Address
Range(LeftColumnSourceRange).Select
Selection.Copy
Range(MatrixColumn & "65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
For Each rcell In Selection
rcell.Value = rcell.Value & "-2"
Next
'Duplicate the Y-ax a second time and mark the duplicated entries with -2
Range(LeftColumnSourceRange).Select
Selection.Copy
Range(MatrixColumn & "65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
For Each rcell In Selection
rcell.Value = rcell.Value & "-3"
Next
'Sort the tripple data in the left column
Range(MatrixColumn & "2").Select
MyWorkSheet = ActiveSheet.Name
MYLastRowNumber = Range(MatrixColumn & "65536").End(xlUp).Row
MyRange = MatrixColumn & "2:" & MatrixColumn & MYLastRowNumber
Range(MatrixColumn & "2").Select
Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.Worksheets(MyWorkSheet).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(MyWorkSheet).Sort.SortFields.Add Key:=ActiveCell.Range _
("A1:A" & MYLastRowNumber - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(MyWorkSheet).Sort
.SetRange ActiveCell.Range("A1:A" & MYLastRowNumber - 1)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(MatrixColumn & "2").Select
'Fill in the Data from Column 3 on the first row
MYLastColAddress = Range(MatrixHeader & "1").End(xlToRight).Address
MYLastRowAddress = Range(MatrixColumn & "65536").End(xlUp).Address
ReadColumn = 3
RowOffset = 0
ListRow = 2
Do Until Cells(ListRow, 1).Value = ""
' Get table entry from third column of list.
TableEntry = Cells(ListRow, ReadColumn).Value
' Get position of product name within range of row titles.
TableRow = Application.Match(Cells(ListRow, 1), _
Range(MatrixColumn & "2:" & MYLastRowAddress), 0)
' Get position of product size within range of column titles.
TableColumn = Application.Match(Cells(ListRow, 2), _
Range(MatrixHeader & "1:" & MYLastColAddress), 0)
Set CellToFill = Range(MatrixColumn & "1").Offset(TableRow + RowOffset, TableColumn)
' If there's already an entry in the cell,
' separate it from the new entry with a comma and space.
If CellToFill.Value <> "" Then CellToFill.Value = _
CellToFill.Value & ","
' Add the new entry to the cell.
CellToFill.Value = CellToFill.Value & TableEntry
ListRow = ListRow + 1
Loop
'Fill in the Data from Column 4 on the second row
ReadColumn = 4
RowOffset = 1
ListRow = 2
Do Until Cells(ListRow, 1).Value = ""
' Get table entry from fourth column of list.
TableEntry = Cells(ListRow, ReadColumn).Value
' Get position of product name within range of row titles.
TableRow = Application.Match(Cells(ListRow, 1), _
Range(MatrixColumn & "2:" & MYLastRowAddress), 0)
' Get position of product size within range of column titles.
TableColumn = Application.Match(Cells(ListRow, 2), _
Range(MatrixHeader & "1:" & MYLastColAddress), 0)
Set CellToFill = Range(MatrixColumn & "1").Offset(TableRow + RowOffset, TableColumn)
' If there's already an entry in the cell,
' separate it from the new entry with a comma and space.
If CellToFill.Value <> "" Then CellToFill.Value = _
CellToFill.Value & ","
' Add the new entry to the cell.
CellToFill.Value = CellToFill.Value & TableEntry
ListRow = ListRow + 1
Loop
'Fill in the Data from Column 5 on the third row
ReadColumn = 5
RowOffset = 2
ListRow = 2
Do Until Cells(ListRow, 1).Value = ""
' Get table entry from fifth column of list.
TableEntry = Cells(ListRow, ReadColumn).Value
' Get position of product name within range of row titles.
TableRow = Application.Match(Cells(ListRow, 1), _
Range(MatrixColumn & "2:" & MYLastRowAddress), 0)
' Get position of product size within range of column titles.
TableColumn = Application.Match(Cells(ListRow, 2), _
Range(MatrixHeader & "1:" & MYLastColAddress), 0)
Set CellToFill = Range(MatrixColumn & "1").Offset(TableRow + RowOffset, TableColumn)
' If there's already an entry in the cell,
' separate it from the new entry with a comma and space.
If CellToFill.Value <> "" Then CellToFill.Value = _
CellToFill.Value & ","
' Add the new entry to the cell.
CellToFill.Value = CellToFill.Value & TableEntry
ListRow = ListRow + 1
Loop
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub






Success, Robert

Excel Macro's en Functies voor blokken met tekst

Ik werk vaak met diverse text of database bestanden waarbij de gegevens niet eenvoudig te splitsen zijn omdat er b.v. spaties voor en achter de gegevens staan of omdat de gebruikersnamen aan elkaar geplakt zijn waarbij ik alleen de hoofdletter van de voor en achternaam kan gebruiken om de namen weer uit elkaar te halen b.v. JanPeterBalkenende moet zijn Jan Peter Balkenende. Ook heb ik soms bestanden waar lege regels in zitten. Natuurlijk kan je, nadat je een extra kolom met oplopende nummers hebt toegevoegd om de oorspronkelijke volgorde terug te krijgen, het geheel sorteren en dan de lege rijen eruit halen, maar een macrotje doet dat wel zo snel. De meeste varianten pas ik regelmatig even aan aan de wens van dat moment, dus zijn deze maco's niet lang het zelfde bij mij. Om ze aan je eigen bibliotheek toe te voegen kun je op [Alt]+[F11] drukken om de VBA editor te openen.

Hier is een overzicht van de verschillende functies:














Sorry for the image of the table, adding a table in html caused some funny behaviour by blogger.


Here are some Macro's and functions which over time I found, modified or wrote my self to manipulate text blocks. Most of them are to split text strings e.g. to split a string on Caps turns JanPeterBalkenende in Jan Peter Balkenende. I often modify these functions as needed.

Again to add these, open your VBA editor by pressing [Alt]+[F11] and select [Alt]+[F8] to select the Macro's or use the "Function" button before the edit line and select "User Created" functions.

====Start of Macro Section======
Sub RemoveSpaces()
' Keyboard Shortcut: Ctrl+Shift+T
' Use Ctrl+Shift+T to Trim all SPACES in a selection, You must assign the Ctrl+Shit+WhateverLetter your self
' by using [Alt] + [F8] in Excel, select the macro and then press Options
For Each r In Selection
a = r.Address
r.Value = Evaluate("Trim(" & a & ")")
Next
End Sub
'=====
Sub DeleteBlankRows()
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
'=====
Function CommaBeforeEveryCap(S As String) As String
'RobertVanDeMaan > Robert,Van,De,Maan
Dim X As Long
CommaBeforeEveryCap = S
For X = Len(CommaBeforeEveryCap) To 2 Step -1
If Mid(CommaBeforeEveryCap, X, 1) Like "[A-Z]" Then
CommaBeforeEveryCap = Left(CommaBeforeEveryCap, X - 1) & "," & Mid(CommaBeforeEveryCap, X)
End If
Next
End Function
'=====
Function SwapOnFirstCap(S As String) As String
'RobertVanDeMaan > VanDeMaan,Robert
Dim X As Long
SwapOnFirstCap = S
For X = 2 To Len(SwapOnFirstCap) Step 1
If Mid(SwapOnFirstCap, X, 1) Like "[A-Z]" Then
SwapOnFirstCap = Mid(SwapOnFirstCap, X) & "," & Left(SwapOnFirstCap, X - 1)
X = Len(SwapOnFirstCap)
End If
Next
End Function
'=====
Function CommaBeforeFirstNum(S As String) As String
'RobertVanDeMaan123 > RobertVanDeMaan,123
Dim X As Long
CommaBeforeFirstNum = S
For X = 2 To Len(CommaBeforeFirstNum) Step 1
If Mid(CommaBeforeFirstNum, X, 1) Like "[0-9]" Then
CommaBeforeFirstNum = Left(CommaBeforeFirstNum, X - 1) & "," & Mid(CommaBeforeFirstNum, X)
X = Len(CommaBeforeFirstNum)
End If
Next
End Function
'=====
Function CommaBeforeFirstCap(S As String) As String
'RobertVanDeMaan > Robert,VanDeMaan
Dim X As Long
CommaBeforeFirstCap = S
For X = 2 To Len(CommaBeforeFirstCap) Step 1
If Mid(CommaBeforeFirstCap, X, 1) Like "[A-Z]" Then
CommaBeforeFirstCap = Left(CommaBeforeFirstCap, X - 1) & "," & Mid(CommaBeforeFirstCap, X)
X = Len(CommaBeforeFirstCap)
End If
Next
End Function
'=====
Function RemoveBeforeLastDot(S As String) As String
'RobertVanDeMaan.com > com
Dim Y As Long
RemoveBeforeLastDot = S
For Y = Len(RemoveBeforeLastDot) To 1 Step -1
If Mid(RemoveBeforeLastDot, Y, 1) Like "." Then
RemoveBeforeLastDot = Mid(RemoveBeforeLastDot, Y + 1)
Y = 1
End If
Next
End Function
'=====
Function RemoveAfterLastDot(S As String) As String
'RobertVanDeMaan.com > RobertVanDeMaan
Dim Y As Long
RemoveAfterLastDot = S
For Y = Len(RemoveAfterLastDot) To 2 Step -1
If Mid(RemoveAfterLastDot, Y, 1) Like "." Then
RemoveAfterLastDot = Left(RemoveAfterLastDot, Y - 1)
Y = 1
End If
Next
End Function
'=====
Function CommaForLastSpace(S As String) As String
'Robert Van De Maan > Robert Van De, Maan
Dim Y As Long
CommaForLastSpace = S
For Y = Len(CommaForLastSpace) To 2 Step -1
If Mid(CommaForLastSpace, Y, 1) Like " " Then
CommaForLastSpace = Left(CommaForLastSpace, Y - 1) & "," & Mid(CommaForLastSpace, Y)
Y = 1
End If
Next
End Function
'=====
Function CommaForFirstSpace(S As String) As String
'Robert Van De Maan > Robert, Van De Maan
Dim X As Long
CommaForFirstSpace = S
For X = 2 To Len(CommaForFirstSpace) Step 1
If Mid(CommaForFirstSpace, X, 1) = " " Then
CommaForFirstSpace = Left(CommaForFirstSpace, X - 1) & "," & Mid(CommaForFirstSpace, X)
X = Len(CommaForFirstSpace)
End If
Next
End Function
'=====
Function CommaForSecondSpace(S As String) As String
'Robert Van De Maan > Robert Van, De Maan
Dim X As Long, MyCount As Long, Occurence As Long
CommaForSecondSpace = S
Occurence = 2
MyCount = 0
For X = 2 To Len(CommaForSecondSpace) Step 1
If Mid(CommaForSecondSpace, X, 1) = " " Then
MyCount = MyCount + 1
If MyCount = Occurence Then
CommaForSecondSpace = Left(CommaForSecondSpace, X - 1) & "," & Mid(CommaForSecondSpace, X)
X = Len(CommaForSecondSpace)
End If
End If
Next
End Function
'=====
Sub ClearTextToColumns()
'Once you used text to columns, Excel remembers this and performs this action on ANY text data that you paste into the spreadsheet.
'To stop this behaviour, you need to reset the text to columns settings.
On Error Resume Next
If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
Range("A1").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:=""
If Range("A1") = "XYZZY" Then Range("A1") = ""
If Err.Number <> 0 Then MsgBox Err.Description
End Sub

======End of Macro Section=====

Klassieke menu in Excel 2007

Nadat ik het Engelstalige klassieke Windows menu op Windows-7 had geïnstalleerd, zodat ik meer controle heb over de indeling en de meeste programma's met een of twee toetsaanslagen kan starten, zocht ik het klassieke menu voor Excel. Voor een deel zie ik het voordeel van de nieuwe menustructuur maar het kost mij veel meer tijd om de juiste tab te vinden en dan het juiste icoontje. Voor de meeste zaken gebruik ik de oude toetsenbord aanslagen. Door dit klassieke menu toe te voegen aan de nieuwe ribbon menu's heb ik beide tegelijkertijd.
Om dit aan Excel toe te voegen open je Excel. Daarna open je de VBA editor met behulp van [Alt] + [F11]. In je persoonlijke bestand (PERSONAL.XLSB) maak je onder Modules b.v. een nieuwe module "Menu" aan. Aan de rechterkant plaats je dan de VBA code van hieronder.


Ik heb deze code oorspronkelijk gevonden op deze site.

Na installatie ziet het er (Engelstalig) zo uit:

Sub MakeOldMenus()
Dim cb As CommandBar
Dim cbc As CommandBarControl
Dim OldMenu As CommandBar

' Delete it, if it exists
On Error Resume Next
Application.CommandBars("Old Menus").Delete
On Error GoTo 0

' Create an old-style toolbar
' Set the last argument to False for a more compact menu
Set OldMenu = Application.CommandBars.Add("Old Menus", , True)

' Copy the controls from Excel's "Built-in Menus" shortcut menu
With CommandBars("Built-in Menus")
.Controls("&File").Copy OldMenu
.Controls("&Edit").Copy OldMenu
.Controls("&View").Copy OldMenu
.Controls("&Insert").Copy OldMenu
.Controls("F&ormat").Copy OldMenu
.Controls("&Tools").Copy OldMenu
.Controls("&Data").Copy OldMenu
.Controls("&Window").Copy OldMenu
.Controls("&Help").Copy OldMenu
End With

' Make it visible. It appears in the Add-Ins tab
Application.CommandBars("Old Menus").Visible = True
End Sub

Meer dan drie kolommen sorteren in Excel

In excel kun je standaard op drie zaken tegelijkertijd sorteren.

Op meer dan drie kolommen tegelijkertijd sorteren kan heel gemakkelijk als je het volgende trucje gebruikt.

Stel je wilt de rijen 1 t/m 20 sorteren op basis van de gegevens in de kolommen A t/m G (7 kolommen).

Naast de laatste kolom G creeer je een extra sorteer kolom (in dit geval kolom H). In Cell H1 plaats je de volgende formule:

Voor de Engelse versie =CONCATENATE(A1,B1,C1,D1,E1,F1,G1)

Voor de Nederlandse versie =TEKST.SAMENVOEGEN(A1;B1;C1;D1;E1;F1;G1)

Kopieeer de formule van cell H1 naar H2 t/m H20.

Sorteren doe je nu door op kolom H te sorteren. Mocht je eerst op kolom B en dan op kolom G willen sorteren, dan pas je gewoon de volgorde in het samenvoegen aan (B1;G1;A1;C1;D1;E1;F1).

Als je ook een deel van de kolommen omgekeerd wilt sorteren, maak je (maximaal) 3 sorteer kolommen met samengevoegde tekst strings. B.v. kolom H is vooruit sorteren, kolom I is achteruit sorteren en kolom J is voor het laatste gedeelte vooruit sorteren. In het standaard sorteer dialoogvenster kies je dan de drie kolommen. Voor kolom I selecteer je dan omgekeerde sorteer volgorde.

Optellen in Excel zonder formules

Dit gebruik ik dagelijks en niet veel mensen kennen het.

Als je snel (tijdelijk) wilt weten hoeveel een aantal cellen bij elkaar opgeteld zijn, kun je natuurlijk een formule gebruiken. Vaak is mij dat te veel werk, zeker als ik alleen snel even wil weten hoeveel dat bij elkaar is.

Jaren geleden, het zal waarschijlijk 2003/2004 geweest zijn, leerde ik ooit van Mr. Excel dat als je met je muis een aantal cellen markeert, dat dan rechts onderin je scherm de som van die cellen staat. In dit plaatje is de som van de geselecteerde cellen 178.

In mijn consulting werk merk ik echter dat er nog veel mensen zijn die dagelijks met Excel werken en die deze handige functie toch niet kennen. Deze functie zit al jaren in Excel, en is in Office 2007 nog veel uitgebreider, en toch moeten veel mensen, net zoals ik, er eerst op gewezen worden voordat ze het zien en herkennen.

Meerdere losse cellen selecteren

Meestal selecteer je met je muis een deel van een kolom of een groepje cellen waarvan je het totaal wilt weten, maar je kunt, zoals ik dat hier laat zien, ook verschillende losse cellen aanklikken door je [Ctrl] toets ingedrukt te houden terwijl je iedere specifieke cel aanklikt die je in de optelling wilt meenemen.

Niet alleen de som maar ook andere berekeningen

Als je met je rechter muistoets op het antwoord rechts onderin klikt kun je instellen of je de som wilt of het gemiddelde, het aantal cellen, de minimum of de maximum waarde etc.

Permanent (wel met een formule)

Als je niet tijdelijk maar permanent ergens de som van een aantal cellen wilt hebben ga je natuurlijk gewoon op de cel staan waar je het antwoord wilt hebben en druk je op het Sigma (Σ) teken in de werkbalk. Daarna kun je of een aan een sluitende reeks selecteren, of weer losse cellen door eerst de [Ctrl] toets inte drukken en ingedrukt te houden terwijl je klikt).

Ik kan bijna geen dag zonder deze handige functie.

Data manipulatie in Excel

Voor mensen die grote hoeveelheden gegevens willen manipuleren in excel kan het gebruik van een kleine macro van Chip Pearson, die CaseConvert heet, nuttig zijn. Nou lijkt het als je naar de naam luisterd alsof je met deze xla alleen maar tussen kleine en hoofdletters kunt kiezen, maar dat is zeker niet waar. Je kunt heel gemakkelijk b.v. alle dubbele spaties ergens uithalen, of alle spaties aan het begin of het einde, of alle getallen met een factor vermenigvuldigen, of b.v. de eerste 3 tekens/cijfers weghalen, etc. Let op! dit is geschreven voor de Engelstalige versie, lees even verder om dit snel aan te passen.

Je kunt het xla programmaatje natuurlijk openen als je het nodig hebt, maar ik heb het in mijn Excel start folder staan zodat dit programma altijd in de menu balk staat (Tools, Convert Text). Omdat in de Nederlandse versie van Excel geen menu "Tools" bestaat zal je deze macro eerst moeten aanpassen. Dit kan gelukkig omdat het gedeelte waarin het menu aangepast wordt niet beveiligd is. Door, nadat CaseConvert is geopend, op [Alt]+[F11] te drukken. Daarna ga je naar het gedeelte ModMain (in linker gedeelte) en pas je de regel

{With Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls.Add}

in de Auto_open() macro aan waarbij je de tekst "Tools"vervangt door een Nederlandse menu titel (en dat is weer afhankelijk van welke versie van Excel (2000/2003/XP/2007) je hebt).

Veel van de zaken die ik hier beschrijf kun je ook oplossen door formules, maar hoewel ik veel gegevens heb geanalyseerd m.b.v. formules, helpt dit kleine programmatje nog steeds geweldig. Let wel op hoe je het afsluit want de laatste instelling wordt onthouden, en dat wil je nog wel eens over het hoofd zien als je het programmatje opnieuw gebruikt.

Probleem met foutmeldingen in VBA opgelost.

Een tijdje geleden had ik mijn laptop met XP professional d.m.v. een ghost image opnieuw opgezet en vanaf die dag werkte CaseConvert niet meer, bummer! Na wat spitten ben ik erachter gekomen dat ik in excel VBA mijn setting had staan op "Break on all errors". Hierdoor leiden ook fouten die na een "On Error Resume Next" instructie stonden tot een zichtbare fout. Nadat ik dit had terug gezet naar "Break on unhandled errors" draait alles weer zoals het hoort.

Het ultime manipuleren, heel intuïtief.... maar toch...

Voor mensen die het geen probleem vinden hun gegevens "op straat" te zetten, kun je ook terecht bij CleanUpData een Web 2.0 site die het wel heel attractief maakt om je gegevens aan hen over te dragen als je snel en eenvoudig grote hoeveelheden aan gegevens wilt aanpassen.