Sorting and deleting redundant value
Solved/Closed
Sharma
-
25 Apr 2010 à 19:46
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 30 Apr 2010 à 22:24
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 30 Apr 2010 à 22:24
Related:
- Sorting and deleting redundant value
- Sorting bookmarks in chrome - Guide
- Deleting skype account - Guide
- What is pagefile.sys and can i delete it - Guide
- Deleting duplicates in excel - Guide
- Deleting a snap before it's opened - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
26 Apr 2010 à 08:42
26 Apr 2010 à 08:42
Sorry. I missed to spot the changed data that you provided.
Here is the macro that would do it
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
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
25 Apr 2010 à 20:41
25 Apr 2010 à 20:41
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"
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
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 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
30 Apr 2010 à 22:24
30 Apr 2010 à 22:24
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
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
