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:


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


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!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Domestic Equity
[/TD]
[TD][/TD]
[TD]Other Equity
[/TD]
[TD][/TD]
[TD]Domestic Fixed Income
[/TD]
[TD][/TD]
[TD]International Fixed Income
[/TD]
[TD][/TD]
[TD]Commodities
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ticker
[/TD]
[TD]Market Value
[/TD]
[TD]Ticker
[/TD]
[TD]Market value
[/TD]
[TD]Ticker
[/TD]
[TD]Market Value
[/TD]
[TD]Ticker
[/TD]
[TD]Market Value
[/TD]
[TD]Ticker
[/TD]
[TD]Market Value
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAPL
[/TD]
[TD]1000
[/TD]
[TD]VNQ
[/TD]
[TD]1500
[/TD]
[TD]DBLTX
[/TD]
[TD]300
[/TD]
[TD]GSDIX
[/TD]
[TD]2500
[/TD]
[TD]SLV
[/TD]
[TD]100
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD]LSBDX
[/TD]
[TD]4000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BAC
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

Peter_SSs,

And, when I save it, I am not able to see what I have just entered.

I have to go back and search my replies/thread, and, then I can go back in?????

 

[Peter_SSs]

You can remove my last reply.

Done

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

..

And, when I save it, I am not able to see what I have just entered.

I have to go back and search my replies/thread, and, then I can go back in?????

I have not heard of others experiencing such problems recently so I suspect the problem is at your end.

In such circumstances, Joe4 usually suggests that ".. issue often resides with a corrupt cookie, and deleting the cookies, cache, and temporary internet files resolves it"

 

[hiker95]

Done



I have not heard of others experiencing such problems recently so I suspect the problem is at your end.

In such circumstances, Joe4 usually suggests that ".. issue often resides with a corrupt cookie, and deleting the cookies, cache, and temporary internet files resolves it"

Thanks, I will give that a try.

 

[danelskibr]

hiker95,

I appologize for not giving you more specific information from the beginning. I am new to the forum and wasnt expecting someone to be kind enough to actually write the code for me. I was expecting some advice at best. I am very grateful for your help and I will use the tools you mentioned to get it right the first time from now on!

I am having difficulty using the links you sent becasue they are blocked by my employer. I will send the workbook to my home computer, and continue from there.

Thanks again for the help. Ill get this done ASAP.

 

[hiker95]

danelskibr,

Thanks for the feedback.

You are very welcome.

Will check on your next reply.