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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 30, 2010 at 10:24 PM
Related:
- Sorting and deleting redundant value
- Deleting snapchat account - Guide
- Sorting bookmarks in chrome - Guide
- Difference between clear chat and delete chat in viber - Guide
- Deleting trending searches - Guide
- How to add new music to ipod without deleting old - Guide
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
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
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 January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 25, 2010 at 08:41 PM
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"
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 January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 30, 2010 at 10:24 PM
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
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
