I'm pretty clever with Regular Expressions and can generally reformat
just about any Excel data by dumping it into a program like EditPlus
and running a ton of RegExp powered Find & Replace operations. However,
this latest spreadsheet has me pulling my hair out.

Here's what I have

a b c d e f
1 job 1 doc # job 2 doc # ... ...
2 sop 1 sop001 sop 3 sop003 ... ...
3 sop2 sop002 sop 4 sop004 ... ...
4 sop 5 sop005
5 sop 6 sop006

Cells a1, c1, e1, etc is a job title
Cells b1, d1, f1, etc is the static text 'Document Number'
Cells a2, a3, a4, etc are names of policies for the job in a1
Cells b2, b3, b4, etc are the policy numbers for the jobs in column a

There can be many jobs (a1, c1, e1, etc) and many policies per job.
(but always 1 policy # for each policy)

I need to get the data formatted as

a b c
1 job 1 sop 1 sop001
2 job 1 sop 2 sop002
3 job 2 sop 3 sop003
4 ... ... ...
5 ... ... ...

Any ideas? I came across David McRitchie's RotateCW macro, which
allowed me to pivot the table by 90 deg CCW (a bit easier to use
RegExp's on) but now Im left with alternating rows of policy #'s and
policies:

a b c d
1 doc # sop001 sop002
2 job 1 sop 1 sop 2
3 doc # sop003 sop004 ...
4 job 2 sop 3 sop 4 ...
5 ... ... ... ...
6 ... ... ... ...

and I'm not sure how to go about merging each group of rows into
something like:

a2 b2 b1 c2 c1 ...
a4 b4 b3 c4 c3 ...
.... ... ... ... ... ...
.... ... ... ... ... ...

which would at least make a good base for doing the rest of the
formatting in RegExp land.

Any help would be UBER appreciated.

Thanks,
Chris Bloom