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.

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.

Without macros or formulas, do the following
with this sample data:
DCR KPD OSK
E Gdb 876 319 779
B Cem 907 401
A Bhh 559 256
L Mub 513 605

Reverse Pivot Table with
http://j-walk.com/ss/excel/usertips/tip068.htm
to get this:

Client Fund Units
E Gdb DCR 876
E Gdb KPD 319
E Gdb OSK 779
B Cem DCR 907
B Cem KPD
B Cem OSK 401
A Bhh DCR
A Bhh KPD 559
A Bhh OSK 256
L Mub DCR 513
L Mub KPD 605
L Mub OSK

Then create a Pivot Table that looks like this:

Sum of Units
Fund Client Total
DCR A Bhh
B Cem 907
E Gdb 876
L Mub 513
KPD A Bhh 559
B Cem
E Gdb 319
L Mub 605
OSK A Bhh 256
B Cem 401
E Gdb 779
L Mub