Excel - How to sort and delete redundant value?

December 2016




Issue


I have been trying to sort our patient data in our daily work-up in the lab as below:

"sample external no" "barcode" "assay" 
4 321456 amf 
9 125487 amf 
10 155886 amf 
15 162533 amf 
45 154878 amf 
48 124569 amf 
1 750122 bupo 
5 156453 bupo 
6 145896 bupo 
10 155886 bupo 
11 114456 bupo 
13 357456 bupo 
15 162533 bupo 
17 789456 bupo 
18 158456 bupo 
45 154878 bupo 
48 124569 bupo 
1 750122 canno 
5 156453 canno 
8 159456 canno 
20 145698 canno 
25 123456 canno 
45 154878 canno 
48 124569 canno 
1 750122 metamfo 
2 112563 metamfo 
15 162533 metamfo 
28 112254 metamfo 
45 154878 metamfo 
48 124569 metamfo 


where this data is half done still amidst the sorting otherwise the first column will be haphazard where I want to get the end of sorting on the basis of column A in ascending order but that's on the basis of column C. If there is a value in column A repeated with different value in the column C I would like it to be sorted as

4 321456 amf 
9 125487 amf 
10 155886 amf, bupo, 
15 162533 amf, bupo, metamfo 
45 154878 amf, bupo, canno, metamfo 
48 124569 amf, bupo, canno, metamfo 
1 750122 bupo, canno, metamfo 
5 156453 bupo, canno 
6 145896 bupo 
11 114456 bupo 
13 357456 bupo 
17 789456 bupo 
18 158456 bupo 
8 159456 canno 
20 145698 canno 
25 123456 canno 
2 112563 metamfo 
28 112254 metamfo


If this does so, finding the sample would be rather easy. Can you please help me.

Solution


Here is the macro that would do it :



Sub sortAndRemove()
Dim lRow As Long
Dim sExtNum As String
Dim sBarCode As String


    Cells.Select
    Selection.Sort _
                Key1:=Range("A2"), _
                Order1:=xlAscending, _
                Key2:=Range("B2"), _
                Order2:=xlAscending, _
                Key3:=Range("C2"), _
                Order3:=xlAscending, _
                Header:=xlYes, _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal, _
                DataOption2:=xlSortNormal, _
                DataOption3:=xlSortNormal
                
   
    lRow = 2
    sExtNum = Cells(lRow, "A")
    sBarCode = Cells(lRow, "B")

    
    Do While (Cells(lRow, "A") <> "")
        
        If Cells(lRow + 1, "A") = sExtNum And (Cells(lRow + 1, "B") = sBarCode) Then
    
            If Cells(lRow, "C") <> "" Then
                Cells(lRow, "C") = Cells(lRow, "C") & ", " & Cells(lRow + 1, "C")
                Rows(lRow + 1).Delete
    
            Else
                Cells(lRow, "C") = Cells(lRow + 1, "C")
                Rows(lRow + 1).Delete
         
            End If
        Else
        
            lRow = lRow + 1
            sExtNum = Cells(lRow, "A")
            sBarCode = Cells(lRow, "B")
        End If
   
    Loop
    
    Cells.Select
    Selection.Sort _
                Key1:=Range("C2"), _
                Order1:=xlAscending, _
                Key2:=Range("A2"), _
                Order2:=xlAscending, _
                Key3:=Range("B2"), _
                Order3:=xlAscending, _
                Header:=xlYes, _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal, _
                DataOption2:=xlSortNormal, _
                DataOption3:=xlSortNormal
                    
  Range("A2").Select
  
End Sub

Note


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - How to sort and delete redundant value? » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.