Arrays and Collections and Dictionaries oh my

Hi everyone. I hope you can give me some high-level guidance on something I'm working on. I'm beginning to learn VBA for Excel and have a specific problem I wish to solve (/ dynamic workbook I want to make) in mind. Part of the workbook will involve taking a list of stock tickers, their associated name, sector, and subsector and putting it in a list somewhere else. For example three lines of data are

AMZN	Amazon.com, Inc.	Consumer Discretionary	Internet & Catalog Retail
KO	Coca-Cola Company	Consumer Staples	Beverages
XOM	Exxon Mobil Corporation	Energy	Oil Gas & Consumable Fuels

There are more than 500 rows like that ^ and they change every once in a while. The first two columns never repeat, while the third and fourth (sector and subsector) repeat often.

I would like to make lists, for example, of everything in Consumer Discretionary. And separately, everything in Beverages. And separately, everything in Oil Gas & Consumable Fuels. My question is what is the best way to implement this?

I've been reading a lot about potential different ways to do this and don't know which will make my life hardest/toughest. Considering the data (e.g. the values in each row will always be associated with each other), should I use...

• A 2D array?
• A collection?
• A dictionary?
• A 1D array with a user-defined type that includes four strings?
• Something else?

I can upload a workbook if you want, but I'm not expecting too much code, if any, in an answer. Let me know if you need more information. Thanks

EDIT: I should clarify that the length and members of all the lists are variable. (Depends on the original list from which everything else is derived...)

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

To add a file to a new post

To add a file to an existing post.

Why not just autofilter?

Autofilter the UI function or autofilter the VBA method?

Wouldn't I need a separate list for each and every output I want? The real input is 3x longer, and there are >=40 lists of outputs... Also, I would like to do some things with the data, like a list of everything, but when the sector changes skip two cells (when the subsector changes, skip one cell), etc. I *think* loading everything into memory is the way to go....

Overall though, I'm trying to learn what is the best use for arrays vs collections vs dictionaries in general. Thanks

You could record a macro using autofilter and autofilter's sorting method. Filter, sort, copy to another sheet, unfilter, filter by something else, sort, copy, ... until you have all of the data you want.