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.

Pic#1

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.

Pic#3

Pic#2

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
Enjoy!
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

                Wend

                Range(Cells(FirstCell, 1), Cells(LastCell, 1)).Select

                Selection.Rows.Group

                i = LastCell + 1

            End If

        Next i

    Next j

End Sub