Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidation of dulpicate rows of data.
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidation of dulpicate rows of data.
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. "Help Please" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidation of dulpicate rows of data.
"Help Please" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking vs Data Consolidation Help!! | Excel Discussion (Misc queries) | |||
consolidation of tables in excel with text and figures | Excel Worksheet Functions | |||
Dealing with data in several columns AND rows | Excel Discussion (Misc queries) | |||
Removing blank rows in a worksheet | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |