Creating new rows and sorting

**AnskyCJ** · 08-22-2010, 06:39 PM

Hi:

I'm stuck in Excel. I don't have much VBA experience, so I was hoping someone here could help me. Currently in my spreadsheet, column Q represents industry types for stocks (say, aerospace/defense, banking, energy, oil, telecom, etc). I already have these stocks sorted by industry. What I'm trying to do is create a macro that will add two blank rows after the last stock in each industry has been listed. So, for example, if the column has:

Banking
Banking
Banking
Banking
Oil
Oil
Energy
Energy
Energy

each on an individual row, I would like to have the macro output it as:

Banking
Banking
Banking
Banking

Oil
Oil
Oil

Energy
Energy
Energy

Pretty simple. Unfortunately, I lack the wherewithal to do it and there are about 4K stocks that I need to organize like this. Please help.

**shg** · 08-22-2010, 06:54 PM

You can do it easily without VBA. First add a formula to an unused column:

Please Login or Register  to view this content.

The below that, list the numbers 1 to N twice:

Please Login or Register  to view this content.

The copy col B over itself as values, and sort everything by col B:

Please Login or Register  to view this content.

Then delete the uneeded column.

It takes way more time to explain than do.

**AnskyCJ** · 08-22-2010, 07:28 PM

So if I had 50 different sectors, I would have to assign each of the sectors a number like you did, and then after all that was done, I would have to go from 1 to 50 twice in column B?