Matching values and consolidating data in several rows into

soesters

New Member
Joined
Oct 13, 2002
Messages
3
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,144,116
Messages
5,722,568
Members
422,447
Latest member
srclife

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top