from 1 column to 3 columns

[catgarfield]

Hi All,

I have 1 column like below:

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

1col.PNG
...
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


3cols.PNG
according to below pattern/rules: 1. there are 3 types of headers: AAA, BBB, 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:

AAA
BBB

or

BBB
CCC


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 2007,2011 etc) or perl, shell, awk etc are all fine as well..

Thx

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic
images dont help and they have in your case different info