Matching values and consolidating data in several rows into

[soesters]

I have a sheet (which is an exported file from a different program) with column A (fund names, 8 in total), and column B (asset ID), and column C (amount owned of that particular asset).

I want to know which assets are held by both Fund1 and Fund2 (same asset ID), and if so, what the amounts are. The same for Fund1 and Fund3, Fund2 and Fund3, etc. Since the assets are traded on a regular basis, the sheet changes constantly (assets disappear and new assets appear).

Now:

Fund 1 Asset03987XD $10,000
Fund 2 Asset03987XD $25,000

The result would be something like:

Fund1 Fund2
Asset03987XD $10,000 $25,000

Any suggestions to automate this process? Thanks in advance!

 

[Damon Ostrander]

Hi soesters,

Here is a macro that does what you describe. This macro assumes there is one header row on the input worksheet (which this code assumes is "Sheet1", but just edit this to change it), and it writes its output to "Sheet2" (which you can also change). It assumes the number of funds is 8, but you can change this as well. Here is the code:

Option Base 1

Sub AssetFunds()
Const MaxFunds = 8
Dim Done(MaxFunds) As Boolean
Dim Asset As String
Dim i As Integer
Dim j As Integer
Dim oRow As Integer
Dim oCol As Integer
Dim InSheet As Worksheet
Dim OutSheet As Worksheet
Set InSheet = Worksheets("Sheet1")
Set OutSheet = Worksheets("Sheet2")
'Put fund headers on output sheet
OutSheet.[a1] = "Asset"
For i = 1 To MaxFunds
OutSheet.Cells(1, i + 1) = InSheet.Cells(i + 1, 1).Value
Next i

oRow = 1
For i = 1 To MaxFunds
If Not Done(i) Then
oRow = oRow + 1
Asset = InSheet.Cells(i + 1, 2)
OutSheet.Cells(oRow, 1) = Asset
For j = i To MaxFunds
If InSheet.Cells(j + 1, 2) = Asset Then
oCol = Application.Match(InSheet.Cells(j + 1, 1), OutSheet.[a1:i1], 0)
OutSheet.Cells(oRow, oCol) = InSheet.Cells(j + 1, 3)
End If
Next j
End If
Next i
End Sub