Data merging question

[KevNewton29]

I have a spreadsheet with 15,130 entries which relate to clients and their fund holdings. Quite a lot of the clients have different fund holdings in the same policy and are shown as a seperate row on the sheet.

Is there a way consolidating the rows thta have the same plan numbers and putting the entries on the fund holding column as seperate columns for each holding

i.e I have 4 rows that have plan number 1234 in funds Fund A (row 1), Fund B (row 2), Fund C (row 3) and Fund D (row 4) and all I require is one row with the columns

Plan number, Fund A, Fund B, Fund C, Fund D

Help!

 

[jim may]

Sounds like a good candidate for a Pivot Table

 

[KevNewton29]

Did think about that but I have 7257 different policies and 1044 different fund types so I can't get it to fit on the sheet

 

[Kaps_mr2]

Yes. lets assume that :-

Col A has your plan numbers
Col B has your Fund names
Col C is a concatenation of A&B : e.g C22 = A22&B22

Col D is the value of the holding.

with your data starting in row 21.

then Col E in row 21 downwards put all the plan numbers.
Col F20 - I20 put FUND A, FUND B etc

=IF(ISNUMBER(MATCH($E21&F$20,$C:$C,0)),INDEX($C:$D,MATCH($E21&F$20,$C:$C,0),2),"")

in cell F21 and down / across. Thanks
Kaps

 

[KevNewton29]

Kaps

Thanks for this but with 1044 fund names I still run out of space on the column front.

I could split the funds between sheets and do vlookup to consolidat ebut I may have to tdo the same procedure agian in a month's time and a more automated option would be better (if possible!)