sort two columns on excel while keeping blank cells

Question

So I trying to sort a list of names that have favorite colors to each of those names. In other words I want to have the sort look like the following example: (A and B correspond columns while #'s correspond rows)

  **A**  **B**                  **A**   **B**
1 Tim    Red                  1 Josh    Black
2        Blue                 2         Yellow
3        Purple               3 Maria   Grey 
4 Josh   Yellow               4         Orange
5        Black                5         Pink
6 Maria  Pink                 6 Tim     Blue
7        Orange               7         Purple
8        Grey                 8         Red

I want it to sort the name first, and wherever that name goes, the colors follow its place and then sort the colors. Is there a way to do this without using VBA since I have no knowledge on how to use VBA. Any help would be very grateful and for the record, this is not for a class assignment.

I am currently using Microsoft Excel 2011 for Mac.

As @pnuts implies, filling in the names makes this a trivial task. To do that, try something like the second suggestion in this datapig post. — Doug Glancy

NickSlash NickSlash · Accepted Answer · 2014-05-28T00:09:23

I'm not all that good with worksheet functions and stuff but I don't think you're going to be able to achieve what you want without using VBA.

Assuming Mac VBA is the same as on windows, the following code should get you started.

The Idea: Make a regular sort work by filling the blank 'name' cells and once the sort has completed remove the extra names. I haven't included the code to do the actual sorting but the two methods below should populate the empty cells and also empty them aferwards.

Public Sub InsertDuplicates()
Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("Sheet1")
Dim Current As String: Current = ""
Dim Row As Integer: Row = 1
' Fill the blank cells in the names column
Do
    If Sheet.Cells(Row, 2).Value2 = "" Then
        ' Break out of the loop
        Exit Do
    End If
    If Sheet.Cells(Row, 1).Value2 = "" Then
        ' No name present, so populate cell with the current name
        Sheet.Cells(Row, 1).Value2 = Current
    Else
        ' A name has been found, set it as the current name
        Current = Sheet.Cells(Row, 1).Value2
    End If
    ' Goto the next row
    Row = Row + 1
Loop

End Sub

Public Sub RemoveDuplicates()
Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("Sheet1")
Dim Current As String: Current = ""
Dim Row As Integer: Row = 1
' Remove unwanted duplicate names in names column
Do
    If Sheet.Cells(Row, 2).Value2 = "" Then
        ' Break out of the loop
        Exit Do
    End If
    If Sheet.Cells(Row, 1).Value2 = Current Then
        ' Row is a duplicate so empty the value
        Sheet.Cells(Row, 1).Value2 = ""
    Else
        ' Row is different from the previous, store the value and continue
        Current = Sheet.Cells(Row, 1).Value2
    End If
    ' Goto the next row
    Row = Row + 1
Loop

End Sub

Public Sub SortList()
' perform the sort (you can record a macro to get the code required)
End Sub

Public Sub DoIt()
' this is the main macro, call this sub to action a sort.
InsertDuplicates
SortList
RemoveDuplicates
End Sub

sort two columns on excel while keeping blank cells

2 Answers