Excel-Sorting when rows are grouped...HELP

[amandarose80]

I have a spreadsheet that needs to be sorted. It contains over 200 listed stocks. But each stock has an additional row of info and a blank line after it. Is there a way to sort the stocks by name without losing the rows after it?

This is what the sheet looks like:

Astock Common Stock
102 Shares ### $###
(blank line)
Bstock Common Shares
102 Shares ### $###

Is there anything to do to avoid sorting the names manually?

 

[GlennUK]

This might sound a little complex, but it will work. Firstly, insert a new column ( say before column A ), and then in the new column, adjacent to your first row of data type a formula like this ...

=B2&"1"
( this formula assumes row 2 is the first row of data )

and then in the cell below this, type this formula ...
=IF(MOD(ROW(),3)=2,B3&"1",LEFT(A2,LEN(A2)-1)&MOD(ROW(),3)+2)
( again assumes row 2 is first row of data )
and then copy this formula down to the bottom of data.
Select the contents of the new column, do Edit/Copy followed by Edit/Paste Special/Values, and then sort using this new column.

If your data starts on a row other than 2, the formulae need to be slightly different. If so, let me know what row the data starts on, and I'll post the appropriate formula.

Hope this works for you.

Glenn.

 

[amandarose80]

The data stars on row 43. I tried to adjust the formula myself but it didn't work.

 

[GlennUK]

Ah, well in A43 have ...
=B43&"1"

and in A44 have ...
=IF(MOD(ROW(),3)=1,B44&"1",LEFT(A43,LEN(A43)-1)&MOD(ROW()+1,3)+2)

and copy it down as far as is needed, then do the Edit/Copy followed by Edit/Paste Special/Values, and then try sorting.

Good luck.

Glenn.

 

[amandarose80]

I get #VALUE! in A44...

 

[GlennUK]

Are you sure you put the =B43&"1" in A43? Did that get the first data item and append a "1" to it?

It's the result of the formula in cell A43 that the formula in cell A44 looks at.

That's the only thing I can think that would cause this without actually seeing this for myself.

Hope that fixes it.

Oh well, logging off for 12 hours now, so you won't get any more responses from me in that time.

Good luck again.
Cheers, Glenn.

 

[amandarose80]

Yes I get the correct result in A43. Let me know if you would like to see the sheet or not.

 

[jancebk]

Perhaps this is over-simplifying the task, but how about deleting the blank rows, sorting it how you want it to be sorted, and then reinserting the blank rows? Yes it's going to be manually intensive, but less complex than coming up with all these formulas that need to be customized for a one time solution...

 

[amandarose80]

There is also information that is needed in the row directly below the stock name, then there is a blank line. The blank line really isn't the issue.

 

[jancebk]

OK - then, if I'm understanding the problem correctly, for each stock there are at least 3 rows... One with the name, one (or more?) with additional data, & a third that is blank.

How about this... For each stock "fill down" the stock name for any of the rows that you want to keep with that stock. Then do your sort, which will keep the rows together. You can then either delete the stock name from where you don't want it to appear, or - in case you might ever want to do this sort again... change the color of the text to white in those cells where you don't want to "see" the name appear.

 

[amandarose80]

DIDN'T THINK OF THAT!!! THANK YOU SO MUCH!!!!