Consolidation of dulpicate rows of data.

[Guest]

I have a simple list of 30K mutual funds (by name) and their associated asset
values. I would like to be able to pull this data into another workbook via
vlookup. However, in my list of funds, some funds are listed more than one
time. How can I manipulate my list of funds such that each fund that appears
multiple times is consolidated into one row and the asset value associated
with that fund is the consolidated total of all the asset values for that
funds occurrance, prior to consolidation?

 

[Guest]

You could use the SUMPRODUCT function for this.

Let's say the mutual funds are in column A (rows 1-100 for this example) and
the asset values are in column B.

=SUMPRODUCT(--(A$1:A$100="Fund1"),(B$1:B$100))

You can replace "Fund1" with a cell address if you have the fund names
already on a sheet.

 

[Guest]

I have a simple list of 30K mutual funds (by name) and their associated asset
values. I would like to be able to pull this data into another workbook via
vlookup. However, in my list of funds, some funds are listed more than one
time. How can I manipulate my list of funds such that each fund that appears
multiple times is consolidated into one row and the asset value associated
with that fund is the consolidated total of all the asset values for that
funds occurrance, prior to consolidation?

You could try using a PivotTable. In the PivotTable layout put the fund name
in the row and the fund value in the data. The PivotTable will then
consolidate the data for you.