Hi Kiran:

without macros do the following

1. make a copy of your workbook
2. on a new sheet in row 1 enter
A B C D E
fundcol clientrow fund client units
3. in row 2 enter
A =IF(ISERROR(IF(A1=XXX,2,A1+1)),2,IF(A1=XXX,2,A1+1) )
B =IF(ISERROR(IF(A1A2,B1+1,B1)),2,IF(A1A2,B1+1,B1) )
C =OFFSET(sheet1!$A$1,0,A2-1)
D =OFFSET(sheet1!$A$1,B2-1,0)
E =OFFSET(sheet1!$A$1,B2-1,A2-1)

replace the XXX with the last column where the fund is
copy it down until you start geting errors. (0s)

4. copy the sheet and paste special values to remove the links
5. sort to remove the blank cells and delete them.

you may need to split the process up if you have too many rows.





--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Kiran Veeramallu" wrote:

I have Number of units held by each client in a table format.

First column has FULL list of clients and first row has full list of Funds.
In the table values are pupulated if a particular client has invested in the
specific fund otherwise left blank.

Now I was to convert the three column format with FUND - Client - Number of
units. A fund can have multiple clients so there can be multiple rows for
each fund.

Can anybody help me what is the best way to do this without writing macro.