How To Use The Dictionary Object In VBA To Change The Structure Of An Excel Table

How To Use The Dictionary Object In VBA To Change The Structure Of An Excel Table
By Andy L Gibson

Frequently you'll be faced with a index wherever the data put together makes it awkward to filter the in turn you need. It doesn't mean the index was backdrop up incorrectly, only to facilitate your analysis wasn't anticipated.

VBA's dictionary object is the ideal tool to streamline the index so it will be more effective. Let's look on an pattern of undergraduate test scores, but it can equally be a listing of customer purchases:

Name Test Score
John Test1 10
Andrew Test1 22
John Test2 12
Andrew Test2 25

While this index might be effective, perhaps you need to chart the comparative scores and this format may possibly be preferable:

Name Test1 Test2
John 10 12
Andrew 22 25

Using The VBA Dictionary Object

The dictionary object is analogous to an array or a collection but with more functionality and is much easier to understand. Once you've backdrop up a dictionary, the basic grammar in support of adding up an article goes something like this:

Dict1.Add type, article

The type property is a unique set great store by in support of the article you're saving to the dictionary. So if you're adding up a undergraduate renown the code might be as follows:

Dict1.Add "john", testscores

We'll be responsibility two things to trade our index of test scores:

Create a dictionary of unique undergraduate names
Associate the test scores with the undergraduate renown
Identifying Unique Values With The Dictionary Object

We'll take plus of the detail to facilitate type morals requirement be unique by creating a listing of undergraduate names and it follows that adding up the test scores to the renown. The objective of the code is to create arrays of undergraduate names and test scores, plus an array of test names.

First, we'll create a dictionary order, first-rate the range and create a flexible to unite the test names.

Set dict1 = New Dictionary
Dict1.CompareMode = BinaryCompare

Sheets(1).Activate
Range("a1").CurrentRegion.Columns(1).Select
Tests = ""

Now we can ring through the index and backdrop morals in support of renown, test renown and mark.

For x = 2 To Selection.Count
MyName = Selection(x).Text
MyTest = Selection(x).Offset(0, 1).Text
MyScore = Selection(x).Offset(0, 2).Value

' add the testname to tests if not already added
If InStr(tests, myTest) = 0 Then tests = tests & myTest & "|"

We'll use up first-rate state of affairs to determine if the undergraduate renown has already been added, and if not, we'll add the renown and the former test mark to the dictionary article in support of the undergraduate.

Then, fatefully, if the undergraduate renown exists in the dictionary we'll only add the mark to the text to facilitate is associated with the student's renown.

Select Case dict1.Exists(myName)
Case False
Dict1.Add myName, myName & "|" & myScore
Case True
Dict1(myName) = dict1(myName) & "|" & myScore
End Select
Next

We've pronto got a progression of pipe "|" bordered text strings to convert into a another index, which you can fix in a integer ways, in support of pattern in a another sheet:

ActiveCell.Value = "Name|" & tests
For Each c wearing dict1
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = dict1(c)
Next

Then we can use up Excel's text to editorial tool or nearly VBA code to convert the another data to a index.