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:
There are 3 types of headers: AAA, BBB and CCC. Next line after them are their corresponding values.
Values of AAA and BBB (that is to say, next lines after them) are not null. ex:
cannot be seen. They will always have values after them.
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.