David has attended:
Excel Advanced course
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Sorting data
I am trying to create a model that sorts stocks. Each stock has a country and a sector. The sectors are always fixed to 11 known sectors. The country's however change depending on the starting universe of stocks. For example if I am looking at UK stocks only it will be limited to 1 country, if I am looking at stocks in Europe there will be c. 30 countries.
I want to create 11 tabs for each sector and then populate the tabs with the stocks. I want to create a different table in each tab depending on the stocks country before populating that table with more data (ie. the price of each stock, latest earnings, etc).
ie.
IT tab
UK Stocks
DE Stocks
FR STOCKS
Pharmaceuticals tab
UK Stocks
DE Stocks
FR Stocks
etc
I am just wondering if anyone can suggest a good way of doing this? My issue is that the number of stocks is always changing and the number of countries will always be different.
RE: Sorting data
Hello again. I thought i'd try and be a bit clearer so i'm uploading a sheet to explain.
The spreadsheet can be downloaded here: https://drive.google.com/open?id=0B2...011RzlCdWZIclk
As you can see the first tab, 'Starting Universe', has all my raw data. Stock ticker, name, sector, industry and country.
Note this data is not fixed. The stocks can change so there may be different industries (though there is a max of 11). There could also be different countries.
The second tab, 'Industry Template', the template file of the data I want to populate.
I am planning on having 11 tabs, one for each industry. On each tab I want a new table to be created for each country, as you can see in the template file. COUNTRY 1, table, COUNTRY 2, table, etc, etc.
I want to create a macro that basically counts how many stocks for each industry in each country then populates the tables in each of the industry tabs with the those stocks.
I am struggling to come up with an idea how to do this and would appreciate any input.
RE: Sorting data
Hello David,
Our VBA Advanced trainer is currently on leave. He is the only one who might be able to help you with this question. He will be back the week after next.
Apologies for the delay!
Kind regards
Marius Barnard
STL
RE: Sorting data
Hello, just to let you know i've solved this issue!
RE: Sorting data
Hi David, thanks for letting us know. Glad you got it working!
