Friday, November 8, 2013

A small cool macro that makes Mind Maps and Spreadsheets better friends

In case, like me, you belong to the Mind Maps lovers’ group, there’s good chance this tool will interest you.
I like Mind Maps because they are easy to create and evolve. They represent data in a way that makes sense to human beings.  
When you want to add a leaf to any branch of the data structure, you don’t need to mess around, as it is just a natural development of the idea representation.

On the other hand, Spreadsheets have their own advantages. They are able to perform calculations on the data, and sort and filter it.

I like to combine both Mind Maps and spreadsheets in my work. I summarize ideas in a Mind Map and move it to an Excel™ spreadsheet in order to use it in a way that involves calculations and filtering.
I use Xmind for creating Mind Maps. Moving the data from the Xmind application to an Excel sheet is very easy: copy-paste the central subject into the sheet. 
However, the data format in the target sheet is not very useful for my goal – each hierarchy is placed in a new column, as you can see in picture #1. 
I would be happier if I were able to have all the data in same column, indented by the hierarchy, as you can see in picture #2. 
It would be even cooler if we were able to use Excel’s “grouping” feature so we could see the exact hierarchic level of the data, see picture#3.
I will not keep secrets from you. I wrote an Excel VBA Macros that provides the wish list above.  Feel free to use it, just copy it into your VBA editor.

Note: while this tool is great for porting the Mind Map data into Excel, once you’ve used it, the data won’t be easily exported back to MindMap. If you find it necessary, you can create a VBA macro that will help to do that.

I created three macros: one that moves the data to one column and indents it according the hierarchy. The other groups the data according to the indentation and the third one which calls both macros.
Before that you run the macro, make sure that the cells that conatins the data are in the "selection" as you can see in the following video:

 If you have  any questions, Tweet me: @testermindset
The VBA code:

Sub moveIndentGroup()
' This macro call the 2 other Macros in order to perform all actions usingcommand

    LastRow = Selection.Cells.Rows.Count
    Call moveAndIndent
    Call GroupIt(LastRow)
End Sub

Sub moveAndIndent()
 Dim rCell As Range
    Dim rRng As Range
    Set rRng = Selection
    For Each rCell In rRng.Cells
       If ((rCell <> "" Or rCell <> 0)) Then
        Cells(rCell.Row, 1).Value = rCell.Value
        Cells(rCell.Row, 1).IndentLevel = rCell.Column - 1
        If rCell.Column > 1 Then rCell.Value = ""
       End If
        Cells(rCell.Row, 1).HorizontalAlignment = xlLeft
    Next rCell
End Sub

Sub GroupIt(Optional LastRow)
    If IsMissing(LastRow) Then LastRow = Selection.Cells.Rows.Count
    For j = 1 To 5
        For i = 1 To LastRow
            If Cells(i, 1).IndentLevel = j Then
                FirstCell = i
                LastCell = i
                While (Cells(FirstCell, 1).IndentLevel <= Cells(LastCell + 1, 1).IndentLevel) And LastCell <= LastRow
                    LastCell = LastCell + 1
                Range(Cells(FirstCell, 1), Cells(LastCell, 1)).Select
                i = LastCell + 1
            End If
        Next i
    Next j

End Sub

No comments:

Post a Comment

Note: In order to avoid SPAM, comments are moderated before they are published.