How can I change a property to a data record?

Hi everyone,

I am not particularly experienced at Pipeline Pilot, and do not have a strong computer/programming background (I even find pilotscript tough!)

What I am currently trying to do in PP is form a compound database by combining many properties from different excel files and merging them together to come up with an excel table with structure shown below:

COL1                                                       COL2               COL3          COL4

Prop1 (Compound)            Prop2     Prop3     Prop4     ...

AAA-000                  NumValueA  NumValueB NumValueC    ...    

AAA-001                  NumValueD  NumValueE NumValueF    ...    

AAA-002                  NumValueG  NumValueH NumValueI    ...    

...

I have succeeded at doing this when the original excel files are organised such as shown above.

I have had problem at doing this when I have am merging 2 worksheets from the same excel file that are not as well organised, i.e. (after merging):

COL1                                                       COL2              COL3          COL4

Statement1 (useless)

AAA-000

Prop2                                NumValueA

Prop3                                NumValueB   

Prop4                                NumValueC

...

After this I have removed excess columns, transposed rows to columns using "Statement1" property (not ideal)  to arrive at this:

COL1                         COL2                    COL3                COL4                COL5

Statement1    AAA-000       Prop2       Prop3       Prop4

(empty)       (empty)     NumValueA   NumValueB   NumValueC

Problem now is that the compound name (i.e. the data record text string) is shown as a property! What I ideally want is this:

COL1                         COL2                                  COL3                COL4                 COL5

Statement1 Compound (Prop1)       Prop2       Prop3       Prop4

(empty)       AAA-000           NumValueA   NumValueB   NumValueC

This gets even more complicated to resolve when I have merged two worksheets together such as below:

COL1                                   COL2              COL3          COL4

Statement1 (useless)

AAA-000

Prop2                        NumValueA

Prop3                        NumValueB   

Prop4                        NumValueC

Statement2 (useless)

AAA-001

Prop2                        NumValueD

Prop3                        NumValueE   

Prop4                        NumValueF

Any help on this problem would be greatly appreciated!

Regards;

Oliver.