Forum Discussion

CDav044's avatar
CDav044
Copper Contributor
Sep 02, 2022

Using Visio Brainstorming data in Excel

Hello, I need some help using data that I export from Visio Brainstorming into Excel. When exporting a mind map to excel, it dumps the data as an XML file with one column of text designating the level in the mind map tree and a second column of the text from each mind map entry. 

I would like to take this data and move it to a table or at least distribute out to columns based on the level it is in the map. e.g. Column A = main topic, Column B = first branches, Column C = second branches and so on, keeping the association with it's predecessor that is designated by the number sequence . 

Data eg. 

desired output eg. (or similar) 

 



excel

2 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 02, 2022

    CDav044  Here is 1 option assuming the number of "." is indicative of the level.  Assume level is in col A, name is in col B, and this is put in col C and fill down:

     

    EDIT: deleted prior formula for this to preserve some of that formatting (maybe?) and a cleaner/easier formula in general:

     

    =LET(level,A1,name,B1,
     CC,LEN(level)-LEN(SUBSTITUTE(level,".",""))+1,
     LL,SEQUENCE(,CC), 
     IF(LL=CC,name,""))

     

Resources