# Sorting and deleting redundant value

Solved/Closed
Sharma - Apr 25, 2010 at 07:46 PM
rizvisa1 Posts 4478 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

Cheers
Sharma

## 2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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, _
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, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Range("A2").Select

End Sub```
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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"
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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