Excel - Formatting (Output) a Sheet in Certain Order

I have a worksheet with four (sections/sheets). Each one contributes to a final sheet with the answers I need to use. The final sheet is not ordered the way I need it. I would like to order it by the numeric in one column (all of the "1"s together, with a sub-order (descending values) in another column. Need a space or two between the breaks on 1, 2, etc. There will be a different amount of rows each time and the sheet needs to consider, and account for this. Maybe do the sort on a 5th sheet. Test copy/file of 4th sheet attached.

22      1
27      1
22      1
21      1                                  Original looks like this column
26      1
27      1
22      1
22      1
27      1
27      2
29      2
22      2
27      2
29      2
29      2
29      2
21      2
22      2
29      3
29      3
21      3
25      3

================

Need them sorted this way

22      1
27      1       highest numbers to the top of the list (of 1s)
22      1
21      1       lowest numbers towards bottom of list
26      1
22      1
22      1
27      1         highest numbers to the top of the list (of 1s)
27      2
29      2         highest numbers to the top of the list (of 2s)
22      2
27      2
29      2        highest numbers to the top of the list (of 2s)
29      2        highest numbers to the top of the list (of 2s)
29      2        highest numbers to the top of the list (of 2s)
21      2        lowest numbers towards bottom of list
22      2
29      3        highest numbers to the top of the list (of 3s)
29      3        highest numbers to the top of the list (of 3s)
21      3        lowest numbers towards bottom of list
25      3

Not sure that's clear, but points awarded for good solutions. Help, PLEASE.
Test-File1.xlsx
mzimermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Have you tried to apply sorting??

What i mean by that is select your data and then go into data and apply sort..first on Column-K and then on Column-L and do the sorting which will do what you are looking for...

Saurabh...
0
DougCommented:
What Saurabh said. Select the entire range that you need to sort, including headings. Then use the sort function on the Data tab. Add the 1st column and sort descending. Then add the 2nd column and sort ascending. Then apply the sort.
0
mzimermanAuthor Commented:
There is code embedded in the cells of the sheet. I want to be able to transfer only 2-3 columns to yet another (sheet 5), do the type of sort you mention, insert blank rows between the 1,2,3 numbers and have the higher values (within each set) appear in descending order (higher values on top). Since this process will be done often, I need it to be dynamic and as automatic as possible. How????
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Saurabh Singh TeotiaCommented:
So if i understand correctly you want to do sort only on few columns which you want to use?? What are those columns?? Also you want to do this sort in new worksheet correct me if im wrong??
0
mzimermanAuthor Commented:
Columns J,K,L are the ones I need on the last sheet (yet to be created). Those columns should be copied to the new sheet. I do not want any sorting done on the (example) sheet I sent.Then sorted by K and then J. Values in J need to be in descending order, larger values on top.If possible, after the sorting, blank rows in between the K column values (all 1s together, all 2s together, etc.). I need this to happen dynamically and automatically since new data will constantly be updated.
0
Saurabh Singh TeotiaCommented:
Okay so just to ensure i understand this correctly this is what you want to do...

1. From you current worksheet you want to copy J,K,L to a new worksheet
2. This worksheet will be created everytime by macro or you will have it? If you have it then what will be the worksheet name?
3. Again also copying will be Column -J will become Column-A in this new worksheet..Column K will become B and Column L will become C in this new worksheet
4. Now you want to sort this worksheet by column B and A..Rather it will be column -A first..larger values in descending order and Column -B will be in ascending order..

Is this is what you are trying to do??

Saurabh...
0
mzimermanAuthor Commented:
If the new worksheet can be created on the fly, it's okay to do that.If it's easier to create it more permanently that's okay too. I just need it sorted as discussed before. Name the new sheet Tricash Analysis-Sorted. If possible, separate the 1s and 2s, etc. columns with values like "22" or "27" should be with highest values at the top, within the 1s column, with separating blank rows. Again, data will change often, so, needs to adjust dynamically.
0
mzimermanAuthor Commented:
Or maybe a completely separate sheet that links to the data in the sheet I provided.
0
Saurabh Singh TeotiaCommented:
So if i understand correctly it will be..

27 first since we are sorting on descending order then as soon all the 27 values get completed a blank row..and then lets say you have 26 all its values and so on...
0
mzimermanAuthor Commented:
The numbers should be descending within each set. The set consists of all of the numbers alongside 1
Then a second set of all the numbers alongside 2
Then a third set of all the numbers alongside 3, and so on.

See attached pdf.
experts-xchg-model.pdf
0
mzimermanAuthor Commented:
It's been a while since the last comments were made (11/4/15). Is someone still working on this?
0
Saurabh Singh TeotiaCommented:
Use this code this will do what you are looking for..

Sub movedata()
Dim lrow As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
lrow = ws.Cells(Cells.Rows.Count, "J").End(xlUp).Row
Sheets.Add after:=Sheets(Sheets.Count)

ws.Range("J1:L" & lrow).Copy
Range("A1").PasteSpecial xlPasteValues

lrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
With ActiveSheet

   .Sort.SortFields.Clear
   .Sort.SortFields.Add Key:=Range("A2:A" & lrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  .Sort.SortFields.Add Key:=Range("B2:B" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A1:C" & lrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
Range("a1").Select

End Sub

Open in new window


Saurabh...
0
mzimermanAuthor Commented:
Looks interesting. How do I run it?
0
Saurabh Singh TeotiaCommented:
Do this..

1. Copy the code..
2. Go to your workbook..Press alt+f11 which will open the vb editor..
3. In VB Editor goto insert-->new module-->Paste the code their..
4. Close the VB editor and come to you workbook.
5. Press alt+f8 in your workbook and run the macro called movedata which will do what you are looking for..

Saurabh...
0
mzimermanAuthor Commented:
I'm not sure that I'm doing this right.

Microsoft Visual basic--
Run-time error '9':
subscript out of range

I'm trying to run this from the last sheet (the sheet I sent you. Does it matter where I run it from?
0
Saurabh Singh TeotiaCommented:
No it doesn't..In your masterworkbook do you have a sheet1 ?? As this is based out of sheet1..if not then change the sheet name in line-5 to the actually sheet name you have..
0
mzimermanAuthor Commented:
Well, it created a new sheet, but... Now they're sorted by total point values, not within the "racenum" value in column 2. Sort has to be first by "racenum", then descending values within "racenum"

for example:

              Racenum = 5  (second column)      Descending Values for Total Points (column 1) within racenum 5 (second column)

       35      5      MEADOWOOD
       31      5      COUP DE GRACE
       31      5      SOCIAL INCLUSION
       31      5      TOP FORTITUDE
       29      5      FAVORITE TALE
       27      5      BAYERN
       27      5      EMBELLISHING BOB
       27      5      PURE SENSATION
       25      5      HAVANA

first column should descend in value.
0
Saurabh Singh TeotiaCommented:
If you want to change the same then move line-16 above the line-17 this will do what you are looking for...
0
Saurabh Singh TeotiaCommented:
I mean this..

Sub movedata()
Dim lrow As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
lrow = ws.Cells(Cells.Rows.Count, "J").End(xlUp).Row
Sheets.Add after:=Sheets(Sheets.Count)

ws.Range("J1:L" & lrow).Copy
Range("A1").PasteSpecial xlPasteValues

lrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
With ActiveSheet

   .Sort.SortFields.Clear
   .Sort.SortFields.Add Key:=Range("B2:B" & lrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
   .Sort.SortFields.Add Key:=Range("A2:A" & lrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  
    With .Sort
        .SetRange Range("A1:C" & lrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
Range("a1").Select

End Sub

Open in new window


Saurabh...
0
mzimermanAuthor Commented:
Getting closer. It's sorting the point values, but the racenum column needs to be in order by:

1
1
1
      blank row
2
2
2
      blank row
3
3
3
       blank row
4
4
4
      blank row

Currently sorting in reverse order;

4
4
4

3
3
3

2
2
2

1
1
1


I need a blank row inserted between the 1s and 2s and 3s, etc.
0
Saurabh Singh TeotiaCommented:
Change line-16 which is this..

.Sort.SortFields.Add Key:=Range("B2:B" & lrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

Open in new window


to this..

.Sort.SortFields.Add Key:=Range("B2:B" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Open in new window


Also please confirm first if this is what you are looking for will change the code to insert a blank line as well..

Saurabh...
0
mzimermanAuthor Commented:
Okay, so except for some unwanted  "0" entries coming up before "racenum" 1, it is sorting correctly. Any way to eliminate those lead "0"s? Need to put blank rows before the "racenum" 1 sets and "racenum" 2 sets, etc.
0
Saurabh Singh TeotiaCommented:
You need to convert your data into number anything which has zero in front of it is not a number...
0
mzimermanAuthor Commented:
I'm not sure where those rows are coming from. I suspect that they are from a dependent sheet in the workbook. Is there a way to eliminate anything with a "0" from appearing in the resultant sheet? Zero is not a valid possibility.
0
mzimermanAuthor Commented:
Or disregard if the contents of column 3 (name) is not alphanumeric. It will always be a name.
0
Saurabh Singh TeotiaCommented:
Quick question..I don't see anything with zero or the value which is equal to zero??

Which one you want to eliminate??

Saurabh...
0
mzimermanAuthor Commented:
It's coming from another sheet. The data comes from multiple inputs. Maybe just let the "0" problem alone. Just blank row it out between the 1s, 2s, 3s, etc.
0
mzimermanAuthor Commented:
Has the blank row issue been solved?
0
Saurabh Singh TeotiaCommented:
Sorry i missed your comment as was busy in couple of things..Now what i understand correctly is that you just want to insert a blank row now whenever the value Changes?? Also can you please confirm which version of the code you are using so that i can make necessary changes in that only..

Saurabh...
0
mzimermanAuthor Commented:
I'm attaching what I think was the last mod. Insert a blank row  when the value changes in

RaceNum
3Race
1
1
1
1
1
1
1
1
1

2
2
2
2
2
2
2
2
2

3
3
3
3
3
3
3
3

4
4
4
4
4
4
4
4
4
4
4

5
5
5
movedata-sent-to-EE-12-7-15.pdf5
5
5
5
5
5
5
5
5
5

6
6
6
6
6
6
6
6
6
6
6
6
6

7
7
7
7
7
7

8
8
8
8
8
8
8
8
8
8

9
9
9
9
9
9
9
9
9
9
9
9
9

10
10
10
10
10
10
10
10
10
10

11
11
11
11
11
11
11
11
11
11
11

12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12

13
13      
13
13
13
13
13
13
13
13
13
13
13
13
13

etc.
0
Saurabh Singh TeotiaCommented:
Use this code...

Sub movedata()
    Dim lrow As Long
    Dim ws As Worksheet

    Set ws = Sheets("Sheet1")
    lrow = ws.Cells(Cells.Rows.Count, "J").End(xlUp).Row
    Sheets.Add after:=Sheets(Sheets.Count)

    ws.Range("J1:L" & lrow).Copy
    Range("A1").PasteSpecial xlPasteValues

    lrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
    With ActiveSheet

        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("B2:B" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("A2:A" & lrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

        With .Sort
            .SetRange Range("A1:C" & lrow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    Range("a1").Select
    Dim i As Long

    i = 2

    Do Until i > Cells(Cells.Rows.Count, "b").End(xlUp).Row

        If Cells(i, "b").Value <> Cells(i + 1, "b").Value Then
            Rows(i + 1).Insert
            i = i + 2
        Else
            i = i + 1
        End If



    Loop
End Sub

Open in new window


This will add blank row basis of B Column whenever the value changes...

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mzimermanAuthor Commented:
Good job! Except for the zeros appearing it works fine. It will suit my purposes very well. You deserve all the points. Thanks a lot.
0
Saurabh Singh TeotiaCommented:
You are welcome..Always happy to help .. :-)

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.