Rows to columns

[Swammi]

Hi there,
I have redundant rows in a table that I would like to consolidate. I know how to create a pivot table that will do this, however, some cells in these rows are not redundant and I would like to split these into independent columns. Any thoughts? Attached is a file that should illustrate before and after transformation.

 

[bosco_yip]

Try

In I2, formula copied across :

=IFERROR(TRANSPOSE(INDIRECT(TEXT(AGGREGATE(15,6,ROW($2:$4)/1%+COLUMN($A$1:$G$1)/(COUNTIF($H2:H2,$A$2:$G$4)=0),1),"R0C00"),0)),"")

Regards
Bosco