Solved Problem with Sorting in Excel-2010

November 2, 2011 at 00:57:36
Specs: Windows 7
I've been looking everywhere and I just can't seem to find the solution to this type of sorting. In brief, First Column has Lotto Numbers (1 to 45) and the second column has number of appearances of each number in the first column. But, for printing purposes, I divided the whole thing into 5 bits (10 numbers in each bit and the last bit containing 5 remaining numbers). Here's a practical example:

#....Appearance....#....Appearance....#....Appearance....#....Appearance....#....Appearance
01........254.........11.........240.........21.........235.........31........199.........41.......250
02........316.........12.........200.........22.........215.........32........234.........42.......278
03........185.........13.........300.........23.........188.........33........200.........43.......195
."..........."............"............"............"............"............"..........."...........44.......165
."..........."............"............"............"............"............"..........."...........45.......218
."..........."............"............"............"............"............"..........."
10........245.........20.........268.........30.........211.........40........269

The problem now is, How do I sort this type of layout? I would like the Appearance column to be sorted from highest to lowest (numbers to follow with it), in this particular layout. Forever thankful for any useful answers here.

PS: Very sorry about the dots, I had to re-edit this and add the dots so to keep it all leveled. Initially I typed spaces but all numbers came next to each other, it looked very messy.

Regards, Robbie


See More: Problem with Sorting in Excel-2010

Report •


#1
November 2, 2011 at 06:25:10
✔ Best Answer
First, a posting tip:

If you click on the blue line at the end of this response you'll find the instructions on how to post data in this forum so that your columns will stay lined up.

As for your question, how about this:

Put all of your #'s and Appearance data in Columns A and B (A1:B46)

In D1:M1 put the Column Headings as you have posted above.

1 - In D2 enter =A2. Drag it into E2, then drag both cells down to E11.
2 - In F2 enter =A12. Drag it into G2, then drag both cells down to G11.
3 - Continue this process until you have a table of formulas that basically spilts your list of 45 numbers into the 5 sets of numbers that you have above.
4 - Select this table and set it as the Print Area
5 - Sort Columns A & B, keyed on Column B (Appearance)

Your table will reflect the sorted data and you can print it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 2, 2011 at 14:20:47
G'day to you Sir, and thank you so much for this answer. I will stick to your suggestions word by word. It's a help, definitely! I was kind of hoping that Excel would have a DIRECT sorting solution to my type of layout. Seems the INDIRECT way (your approach) is the only way.

And, thank you for the info in regards to my posting - it will be implemented next time around, fo' surrrr. Yup, it took me ages to do all those dots, lol.

Many regards from Australia, Robbie


Report •
Related Solutions


Ask Question