Sorting and deleting redundant value

Solved/Closed
Sharma - Apr 25, 2010 at 07:46 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Apr 30, 2010 at 10:24 PM
Hi,

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.

Cheers
Sharma

2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 26, 2010 at 08:42 AM
Sorry. I missed to spot the changed data that you provided.
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
2
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 25, 2010 at 08:41 PM
Sharma

Unless I am missing some thing, all you want to do is sort by column C, so am i missing some thing here. If thats all you want. Select column A, B and C and sort. If you data has a header, make sure you checkoff the box that says "my data has header"
0
Hi rizvisa1,

Thanks a lot. The code works in the awesome manner. That's what I have always looked for. As well, (if I am not tweaking) is there any possibility that when the sorting has been done can the field data in the column C say, AMF, BUPO, CANNO, METAMFO be just be jumbled say like my priority is with CANNO then with METAMFO and BUPO and lastly with AMF, which I mean as follows:

ColA ColB ColC ColD ColE ColF ColG ......
4 321456 , , , amf
9 125487 , , , amf
10 155886 , , bupo , amf,
15 162533 , metamfo, bupo, amf,
45 154878 canno, metamfo, bupo, amf,,
48 124569 canno, metamfo,bupo, amf,
1 750122 canno, metamfo, bupo,
5 156453 canno, , bupo,
6 145896 , , bupo ,


I think you got me. The field data in the column C is sorted in the ascending order from A to Z while I am looking further to get the order jumbled up to make my priority in the first column or say in the order of my choice.

Thank you once again for providing the code for the macro.

Sharma
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 30, 2010 at 10:24 PM
Not directly possible The best possible way I see is that on a separate sheet you define your sort order

like
canno 1
metamfo 2
bupo 3
amf 4


Then its is possible to leverage this information to have the data come out as you want
0