I want to sort a list of strings based on values of adjacent cells excel

Question

I have a list of song titles in one column, and the column next to it contains the number of times that song has been played. I want to order the play count in a separate column and also display the title of the song next to it. Here is what my excel sheet looks like:

Title        | Play Count
------------ | -------------
title 1      |  2
title 2      |  1
title 3      |  
title 4      |  3

Notice that titles that have not been played yet are blank in the play count column. This is what I want it to look like.

Title        | Play Count
------------ | -------------
title 4      |  3
title 1      |  2
title 2      |  1
title 3      |

I want this information to show up in new columns so that the original list and play count column remain untouched. So I don't want this done in a pivot table. Preferably it would only return the top 20 or so song titles, but that is not essential. The list is about 250 songs long.

Let us know if this is what you are looking for or if there is anything I might be missing: http://stackoverflow.com/questions/37239107/sort-range-without-sorting-it-in-a-spreadsheet/37239386#37239386 — Ralph, Jun 23 '16 at 22:12

score 0 · Answer 1 · answered Jun 24 '16 at 06:27

you can try this:

Option Explicit

Sub main()
    Dim dataCols As Range
    Dim columnsStrng As String

    columnsStrng = "C:D" '<--| change "C:D" with your actual "Title" and  "Play Count" columns indexes (they must be contiguous columns)
    With Worksheets("MySongs") '<--| change "MySongs" with your actual sheet name
        Set dataCols = .Range(columnsStrng).Resize(.Cells(.Rows.Count, .Range(columnsStrng).columns(1).Column).End(xlUp).row)
        With dataCols.Offset(, .UsedRange.columns.Count)
            .Value = dataCols.Value
            .Sort key1:=.columns(2), Header:=xlYes
        End With
    End With
End Sub

I want to sort a list of strings based on values of adjacent cells excel

1 Answers1