Sorting data into groups using formulas in excel

[KazumaX122]

Hello,

I am new to the forums and signed up after finding some very helpful guides. Thank you all who contribute. I have a sorting issue that I need some help on. I will give an example:

Sorting.xlsx


What I want is to sort the original data first by GICS code so that all the right GICS are together. Second, I wanted to sort the data by revenue so that it would like like the above example. I have tried using the rank function and a multitude of other formulas but cannot get it to work. I want to avoid using the sort button because everything else I have built for this spreadsheet have been with formulas. Any help on this would be very much appreciated. If I am not clear, please let me know and I will try to explain it in another way.

Thanks!!

[MarvinP]

Hi KazumaX122 and welcome to the forum,

A Pivot Table can do this easily, if you will accept a switch of two of the columns. See the attached where I've made a Pivot of your data. You will need to learn a little about formatting pivots but no formulas are needed to get my results.

[KazumaX122]

Hello Marvin,

Thank you for the warm welcome and the very fast reply! I appreciate it. I have never used pivot tables in excel before and am unfamiliar with them. I can give that a try and see. I was wondering if you know of a formula I can write that can do this for me as opposed to the pivot table? The reason is that, I am downloading raw data from a third party and that data contains thousands of tickers with different GICS code classifications. I was hoping I could download the data and paste value them into a set of cells and then have a formula automatically sort them and spit out the results in another set of columns for me. That's the idea I had going. Thanks again for the quick reply. I am looking into the pivot table solution now.

[KazumaX122]

Marvin,

I tried using the pivot table, but I was not very successful using it. I am much more comfortable using formulas. Do you or anyone else know of a formula I can write to sort the data I have? Thanks for all the help.

[MarvinP]

Hi KazamuaX122,

You will need to refresh the Pivot table if the data changes to show the current results. Because you have 2010 Excel, Pivots are much easier to deal with. Perhaps it is time to learn a little about Pivots?

It always amuses me when people ask questions and then redefine the answers with I don't know how to do that. So - we need to find common ground on how to answer the questions using only the tools the OP knows about. See http://www.youtube.com/watch?v=lEPmBuyeIxs