Experts Required..

[PANKAJUTEKAR]

cAN ANY ONE PLS SUGGEST?

P2-150-EA-001-KM01
C-LU-150-EA-001-KM02-1
P1-150-EA-001-KM01
P1-150-EA-001-KM02
C-LU-150-EA-001-KM01-1
P2-150-EA-001-KM02
C-LU-150-EA-001-KM01-2

cAN THIS GROUP IT by according to their tags?

Kind suggestion require

 

[anandvarma]

what is the relation of data

 

[PANKAJUTEKAR]

Thank You very much for ur reply.

P2-150-EA-001-KM01 this are the tags.

You can see below -
P1-150-EA-001-KM01
P2-150-EA-001-KM01
C-LU-150-EA-001-KM01-1
C-LU-150-EA-001-KM01-2

This is perfect data. The data should come with their respective tags one another.

My data is - like this,
P2-150-EA-001-KM01
C-LU-150-EA-001-KM02-1
P1-150-EA-001-KM01
P1-150-EA-001-KM02
C-LU-150-EA-001-KM01-1
P2-150-EA-001-KM02
C-LU-150-EA-001-KM01-2

i used grouping technique, by using numbering system in col AX.
My grouping also done.
Now my o/p is -

C-LU-150-EA-001-KM01-1
P2-150-EA-001-KM01
C-LU-150-EA-001-KM01-2
P1-150-EA-001-KM01

this o/p for every tag is coming that mean my grouping is done.

but u can see, there are not in proper asceding order. that is not i am able to do.

i show u the code - Plese suggest me for this.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim obj As Worksheet
Dim i
Dim k, mLoop As Boolean, mloop1 As Boolean
Dim lintRow As Long
Dim xlwksh As Worksheet
On Error GoTo errorLoop
mLoop = False
mloop1 = False

For k = 1 To Worksheets.Count
'Debug.Print k & " ---" & Worksheets.Item(k).Name & " ----- " & Worksheets.Count
Worksheets.Item(k).Activate

If ((UCase(Worksheets.Item(k).Name) = "NOTES") Or (Worksheets.Item(k).Name = "Cover page") Or (Worksheets.Item(k).Name = "SPReport_Definition") Or (Worksheets.Item(k).Name = "SPReport_OriginalSheet1")) Then
Worksheets.Item(k).Tab.ColorIndex = 4
Else
Set xlwksh = ThisWorkbook.Worksheets(k)

xlwksh.Name = "Cable list" & "(" & ThisWorkbook.Worksheets(k).Cells(5, 3) & ")"


Range("a11").Select
Range(Cells(11, 1), Selection.End(xlDown)).Select
p = Selection.Rows.Count
Debug.Print p

For i = 11 To p

'If (Left(Cells(i, 2), 1) = "6" And Mid(Cells(i, 2), 3, 1) = "1") Then Cells(i, 50) = 1
'ElseIf (Left(Cells(i, 2), 1) = "6" And Mid(Cells(i, 2), 3, 1) = "2") Then Cells(i, 50) = 2
'ElseIf (Left(Cells(i, 2), 1) = "6" And Mid(Cells(i, 2), 3, 1) = "-") Then Cells(i, 50) = 3
'ElseIf (Left(Cells(i, 2), 1) = "P" And Mid(Cells(i, 2), 2, 1) = "1") Then Cells(i, 50) = 4
'********
'
If (Left(Cells(i, 2), 1) = "P" And Mid(Cells(i, 2), 2, 1) = "1") Then
Cells(i, 50) = 1
ElseIf (Left(Cells(i, 2), 1) = "P" And Mid(Cells(i, 2), 2, 1) = "2") Then
Cells(i, 50) = 2
ElseIf (Left(Cells(i, 2), 1) = "P" And Right(Cells(i, 2), 1) = "1") Then
Cells(i, 50) = 3
ElseIf (Left(Cells(i, 2), 1) = "P" And Right(Cells(i, 2), 1) = "2") Then
Cells(i, 50) = 4
ElseIf (Left(Cells(i, 2), 1) = "P" And Right(Cells(i, 2), 1) = "3") Then
Cells(i, 50) = 5
'ElseIf (Left(Cells(i, 2), 1) = "P" And Right(Cells(i, 2), 1) = "4") Then Cells(i, 50) = 9
'ElseIf (Left(Cells(i, 2), 1) = "P" And Mid(Cells(i, 2), 2, 1) = "-") Then Cells(i, 50) = 10
ElseIf (Left(Cells(i, 2), 1) = "C" And Right(Cells(i, 2), 1) = "1") Then
Cells(i, 50) = 11
ElseIf (Left(Cells(i, 2), 1) = "C" And Right(Cells(i, 2), 1) = "2") Then
Cells(i, 50) = 12
'Else: Cells(i, 50) = 13
End If
Next i



Range(Cells(11, 1), Cells(p + 10, 50)).Select

Selection.sort Key1:=Range("AW11"), Order1:=xlAscending, Key2:=Range( _
"AQ11"), Order2:=xlAscending, Key3:=Range("AX11"), Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Worksheets.Item(k).Tab.ColorIndex = 4
Range("B11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="/2", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="/1", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next k
Exit Sub

 

[Sektor]

I think the real tag is 150-EA-001-KM01. Correct?
Did you try just to sort it?

 

[PANKAJUTEKAR]

Exact Sir.
actully before i was sorted this tag only.
but this tags have some extensions before like p-, p-1, p-2, p-sometag-1, p-sometag-2

this i cant able to do.

that is why i go with above code?

Now i have not understand how to go/ proceed?
that is y, i disrtubing you?
pls require some solution.

 

[Sektor]

Just select cells, right-click and select Sort -> Sort A-Z