Automatically Consolidating Data Using Macro or Formulas

[danelskibr]

Hello, and thank you in advance for the help!

I have data that I pull and copy into an excel sheet every day. I would like this data to be consolidated and rearranged automatically to make it easier to use. This is what the data looks like when I paste it into excel:

	F	G	H	I	J	K	L	M
1	Asset Class	Asset Description	Asset ID 1	Asset ID 2	Market Value	Quantity	Price	%
2	Domestic Equity	Apple	USELESS	AAPL	1000	10	22	10%
3	International Fixed Income	Goldman International	USELESS	GSDIX	2500	2	33	25%
4	Domestic Equity	AT&T	USELESS	T	100	3	54	1%
5	Domestic Equity	Bank of America	USELESS	BAC	500	5	62	5%
6	Domestic Fixed Income	Doubline Total Return	USELESS	DBLTX	300	6	32	3%
7	Other Equity	Vanguard REIT	USELESS	VNQ	1500	15	12	15%
8	Domestic Fixed Income	Loomis Sayles	USELESS	LSBDX	4000	20	35	40%
9	Commodities	Silver ETF	USELESS	SLV	100	15	12	1%

<tbody>
</tbody>

On a seperate tab in the same worksheet, I would like to have this data automatically converted into a more usefull format. Ideally, column F (Asset Class) would be in columns, and the securities would be listed under their respective asset class along. The example below would be a great start.


I would like to stay away from pivot tables if at all possible. Like I said before, the reformatting must be completed automatically when I paste the data or by clicking a macro button.

THANKS FOR THE HELP!

Domestic Equity		Other Equity		Domestic Fixed Income		International Fixed Income		Commodities
Ticker	Market Value	Ticker	Market value	Ticker	Market Value	Ticker	Market Value	Ticker	Market Value
AAPL	1000	VNQ	1500	DBLTX	300	GSDIX	2500	SLV	100
T	100			LSBDX	4000
BAC	500

<tbody>
</tbody>

 

[hiker95]

danelskibr,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


You can change the raw data worksheet name in the macro.


Sample raw data in worksheet Sheet1:

Excel 2007
	F	G	H	I	J	K	L	M
1	Asset Class	Asset Description	Asset ID 1	Asset ID 2	Market Value	Quantity	Price	%
2	Domestic Equity	Apple	USELESS	AAPL	1000	10	22	0.1
3	International Fixed Income	Goldman International	USELESS	GSDIX	2500	2	33	0.25
4	Domestic Equity	AT&T	USELESS	T	100	3	54	0.01
5	Domestic Equity	Bank of America	USELESS	BAC	500	5	62	0.05
6	Domestic Fixed Income	Doubline Total Return	USELESS	DBLTX	300	6	32	0.03
7	Other Equity	Vanguard REIT	USELESS	VNQ	1500	15	12	0.15
8	Domestic Fixed Income	Loomis Sayles	USELESS	LSBDX	4000	20	35	0.4
9	Commodities	Silver ETF	USELESS	SLV	100	15	12	0.01
10
Sheet1

After the macro in a new worksheet Results:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Domestic Equity		Other Equity		Domestic Fixed Income		International Fixed Income		Commodities
2	Ticker	Market Value	Ticker	Market Value	Ticker	Market Value	Ticker	Market Value	Ticker	Market Value
3	AAPL	1000	VNQ	1500	DBLTX	300	GSDIX	2500	SLV	100
4	T	100			LSBDX	4000
5	BAC	500
6
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ConsolidateAssets()
' hiker95, 12/31/2014, ME826581
Dim w1 As Worksheet, wr As Worksheet
Dim c As Range, t As Range, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Sheets("Results")
With wr
  .UsedRange.Clear
  With .Range("A1").Resize(, 10)
    .Value = Array("Domestic Equity", "", "Other Equity", "", "Domestic Fixed Income", "", _
        "International Fixed Income", "", "Commodities", "")
    .Font.Bold = True
  End With
  .Range("A2").Resize(, 10).Value = Array("Ticker", "Market Value", "Ticker", "Market Value", _
        "Ticker", "Market Value", "Ticker", "Market Value", "Ticker", "Market Value")
End With
With w1
  For Each c In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
    If c <> "" Then
      Set t = wr.Rows(1).Find(c.Value, LookAt:=xlWhole)
      If Not t Is Nothing Then
        nr = wr.Cells(wr.Rows.Count, t.Column).End(xlUp).Row + 1
        wr.Cells(nr, t.Column).Resize(, 2).Value = c.Offset(, 3).Resize(, 2).Value
      End If
    End If
  Next c
End With
With wr
  .Columns("A:J").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ConsolidateAssets macro.

 

[hiker95]

danelskibr,

the reformatting must be completed automatically when I paste the data or by clicking a macro button.

See the below link:

http://www.mrexcel.com/forum/excel-questions/69224-creating-command-button-excel.html

 

[Peter_SSs]

  .Range("A2").Resize(, 10).Value = Array("Ticker", "Market Value", "Ticker", "Market Value", _
        "Ticker", "Market Value", "Ticker", "Market Value", "Ticker", "Market Value")

Not too bad with only 10 columns but an easily expandable way to repeat headings like that:

Const NumCols As Long = 50
Range("A2").Resize(, NumCols).Value = Split(Mid(Replace(Space(NumCols / 2), " ", ",Ticker,Market Value"), 2), ",")

 

[hiker95]

Not too bad with only 10 columns but an easily expandable way to repeat headings like that:

Const NumCols As Long = 50
Range("A2").Resize(, NumCols).Value = Split(Mid(Replace(Space(NumCols / 2), " ", ",Ticker,Market Value"), 2), ",")

Thank you, and, Happy New Year.

 

[danelskibr]

1. I am using Excel 2010 on Windows 7
2. I am using a PC.

Thank you for your help! The code works flawlessly and does exactly what I showed in my example. I am really impressed! The framework you gave me will be the building block for many other sheets.

I would like to work with the code a little to edit the formatting and make it look a little prettier among a few other things. One thing I would like to do is add another column next to the "Market Value" column, that will show what % the asset is of the total portfolio (basically just pull the same data as the "Market Value" only with the % value). I was hoping to be able to disect your code and customize it further, but I am having a difficult time understanding what is going on in a certain section. Do you think you could explain this part to me?

With w1
  For Each c In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
    If c <> "" Then
      Set t = wr.Rows(1).Find(c.Value, LookAt:=xlWhole)
      If Not t Is Nothing Then
        nr = wr.Cells(wr.Rows.Count, t.Column).End(xlUp).Row + 1
        wr.Cells(nr, t.Column).Resize(, 2).Value = c.Offset(, 3).Resize(, 2).Value
      End If
    End If
  Next c
End With

Thanks again!

 

[danelskibr]

Peter, when I replaced hiker95's code with this section it worked, but it put the "Market Value, Ticker" data into all 50 columns instead of only the columns that were applicable. Is there a fix for this? Thanks for your help.

 

[hiker95]

danelskibr,

The code that Peter posted was for the titles in row 2.

Thank you for your help!QUOTE]

Thanks for the feedback.

You are very welcome. Glad I could help.

The code works flawlessly and does exactly what I showed in my example.[/

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

It sounds like your raw data structure, and, maybe even the results have changed.

So that we can get it right on the next try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[Peter_SSs]

Peter, when I replaced hiker95's code with this section it worked, but it put the "Market Value, Ticker" data into all 50 columns instead of only the columns that were applicable. Is there a fix for this? Thanks for your help.

I was giving hiker a general example of how you could fill a lot (any number) of columns with the same headers. It was not related to your specific circumstances. To apply that code for your 10 columns you would need to change the 50 to 10 in the following line.

Const NumCols As Long = 50


@hiker
I haven't studied them in detail to see if they are different, but can I delete one of your last 2 posts? If it matters, which one to delete?

 

[hiker95]

Peter_SSs,

You can remove my last reply.

I have been having a problem with trying to edit a reply before the 10 minute time out.