Transform single column to multiple columns based on values

Question

I have 1 column like below:

AAA
999888
BBB
1111
BBB
2222
AAA
999777
BBB
3333
AAA
999666
BBB
4444
CCC
Doit
...

Consider above up to a few thousand rows.
I want to convert it to below 3 column table with rows from an AAA to the next AAA line:

AAA     BBB     CCC
999888  1111    
999888  2222    
999777  3333    
999666  4444    Doit

According to below pattern/rules:

1. There are 3 types of headers: AAA, BBB and CCC. Next line after them are their corresponding values.

2. Values of AAA and BBB (that is to say, next lines after them) are not null. ex:

   AAA
   BBB

   or

   BBB
   CCC

   cannot be seen. They will always have values after them.

3. CCC column header may not be seen between 2 AAA entries as in the example above. From one AAA to the next AAA if there is no CCC, the new column of CCC will be empty for that row.

What is the way to perform this in Excel (any version)? Perl, shell, awk etc. are all fine as well.

Answer 1

I have used

#!/bin/bash
input_file=${1}
declare -A columns
SEP='\t'
curr_level=0
headers=""
while read line; do
if [ -n "${column}" ]; then
    for (( i=${curr_level}; i<${columns[$column]}; i++ )); do
        echo -ne ${SEP} >> tmpout.txt
    done
    curr_level=${columns[$column]}
    echo -n ${line} >> tmpout.txt
    column=""      
else
    column="${line}"
    if [ -z "${columns[$line]}" ]; then
        columns[$line]="${#columns[@]}"
        headers+="${line}${SEP}"
    elif [ ${columns[$line]} -le $curr_level ]; then
        echo "" >> tmpout.txt
        curr_level=0
    fi
fi             
done < $input_file

echo -e $headers | cat - tmpout.txt >> out.txt
unset columns column headers
rm tmpout.txt

Put that into a file, say script.sh, make it executable and call script.sh file_to_format.sh. It will create a file called out.txt, containing the result, and a file called tmpout.txt, that will be deleted, it is used for temporary working out.

Note: This will leave vertical gaps, such as

AAA     BBB     CCC     
999888  1111
        2222
999777  3333
999666  4444    Doit