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.

Answer 1

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

Answer 2

This can be done without VBA, using helper columns and a manual sort.

If you want to do this all the time instead of just once, you may want to consider changing your data architecture. Entering the name in a raw data sheet on every row, then build a pivot table that shows the sorted layout you are after.

Steps to do the sort manually:

Insert a header row in row 1 and put in labels for each column, i.e. Name, color. Add another column and use this formula in cell C2 (copy down):

=IF(ISBLANK(A2),C1,A2)

Copy column C and paste over itself with Paste Special > Values. Now there are the names only in this column. Use the sort dialog to sort by the new column first and by the color second. These are the settings before confirming the sort:

After the sort, you will see this:

Add another formula column with this formula starting in D2 and copied down:

=IF(C2<>C1,C2,"")

Copy column D, paste as values over column A. Delete the helper columns.