Populate Table from Column to Row

[ermengard]

Hi Guys,

Please help me populate the the table in where in the header is in row, in reference to the values of the corresponding values in the first column.

I have attach the excel file.

Table_A.xls

Thanks.

[Punnam]

Please Login or Register  to view this content.

Copy and paste the formula in E2 after that use Ctrl+Shift+Enter & drag it both Vertical & horizontal

Punnam

[Fotis1991]

Hello

You can use advanced filter, or a formula to create a unique list and then COPY>>PASTE SPECIAL>TRANSPOSE>>OK.

To do what you need.

[Pete_UK]

Put this formula in C2:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

and copy down beyond your data to accommodate new data being added. Then you can use this formula in cell E2:

=IF(ISNA(MATCH(E$1&"_"&ROWS($1:1),$C:$C,0)),"",(INDEX($B:$B,MATCH(E$1&"_"&ROWS($1:1),$C:$C,0))))

which can be copied into F2:G2, and then the block of formulae E2:G2 can be copied down as far as you need them - the attached file shows this.

Note, as your profile shows Excel 2003 I've made the formula compatible with this, though it could be simplified by using IFERROR if you now have a later version of Excel.

Hope this helps.

Pete

[ermengard]

Thank you very much