Rearrange Data In Hierarchy To Table

How do I use VBA to rearrange the data from the hierarchy format of Table 1 into the flat format of Table 2. See attached file >Data_Belinda_June-5-07.xls

Table 1: Each record has information arranged in a hierarchy format.
Level 1 information is indented by one space on one line; Level 2 information is indented by 2 spaces on the next line, etc.

Table 2: Data from Table 1 have been rearranged into 7 columns. Another column has been added to create a field for the Reference ID.

Reference ID: Two types: i) Created by prefixing with the letters BI , adding the first four letters from column 4 and the first four letters from column 5; ii) As in (i) with the addition of the entire word from column 6.

The report I have may have more than one hundred records and there may be more than ten people within each section.