Search code examples
excelexcel-formulaexcel-2013

How to sort one column data and match to another column data with some cells was merged in Excel

I am trying to sort one colum data and match to another column data in Excel 2013. I dont know how to explain more because i was really amateur using excel, so i have table like this :

|Date      | Technician |    Task    |
|----------|------------|------------|
|01/01/2020|          CLOSED         |
|02/01/2020|Cindy       |Check       |
|02/01/2020|Cindy       |Repair      |
|03/01/2020|Cindy       |Check       |
|03/01/2020|Cindy       |Maintenance |
|04/01/2020|Joe         |Repair      |
|05/01/2020|          CLOSED         |
|06/01/2020|Joe         |Maintenance |
|06/01/2020|Steph       |Check       |

and im trying to sort and match like this :

|Date      | Technician |    Task    |
|----------|------------|------------|
|01/01/2020|          CLOSED         |
|02/01/2020|Cindy       |Check       |
|02/01/2020|Cindy       |Check       |
|03/01/2020|Cindy       |Maintenance |
|03/01/2020|Cindy       |Repair      |
|04/01/2020|Joe         |Maintenance |
|05/01/2020|          CLOSED         |
|06/01/2020|Joe         |Repair      |
|06/01/2020|Steph       |Check       |

usually im using database sql and if it was in SQL Query it should be :

select technician, task from employee order by technician, task asc;

But for now im not using database sql, just excel. Hope someone will help and sorry for my english too.

Solution

  • Welcome to SO. This is all doable using Excel formulas (as per your excel-formula tag), but I think the easiest way is to actually use native Excel functionality.

    Step 1 - select your data (I assume that your data is stored in cells A1:B8)

    Step 2 - go to Home > Sort & Filter > Custom Sort... - check "My data has headers" option and add the first level (Technician), then use "Add Level" button and add the second level (Task):

    enter image description here

    Here's your final result:

    enter image description here

    Edit - in order to unmerge all cells in column B you need to run the following code:

    Sub UnMergeCells()
    Dim lngRows             As Long
    Dim lngRow              As Long

    With Sheets("Sheet1")
        lngRows = .UsedRange.Rows.Count

        For lngRow = 2 To lngRows
            If .Range("B" & lngRow).MergeCells Then
                .Range("B" & lngRow).MergeArea.UnMerge
            End If
        Next lngRow
    End With
End Sub

    You can then follow the three steps that I described in my original post ("Custom Sort"). After you're finished, you can merge all cells back to the previous state by running this code:

    Sub MergeCells()
    Dim lngRows             As Long
    Dim lngRow              As Long

    With Sheets("Sheet1")
        lngRows = .UsedRange.Rows.Count

        For lngRow = 2 To lngRows
            If .Range("B" & lngRow).Value = "CLOSED" Then
                .Range("B" & lngRow & ":C" & lngRow).Merge
            End If
        Next lngRow
    End With
End Sub