How Do I Do A Custom Sort

[Dazzawm]

I want to sort a file by a particular column first. I need it to be sorted with a particular word first but that word may be anywhere within the cell.

Obviously the normal sort would do it by the first letter of the first word in the cell either A-Z or Z-A. But I need it to look for a particular word in the cell and have that first. Is it possible?

 

[bertie]

What about the sheet name?
The code loops until it finds an empty cell, are there values in the cells?
See highlighted below for edits.

   Set rng = Sheets("Sheet1").Range("A2")
   Do Until rng = ""
      For i = LBound(arr) To UBound(arr)
         If InStr(1, rng.Value, Trim(arr(i)), vbTextCompare) Then
            Sheets("Sheet1").Range("AL" & rng.Row).Value = i + 1
         End If
      Next
      Set rng = rng.Offset(1, 0)
   Loop

 

[Dazzawm]

It is called sheet1, and there are no empty cells in either column C or AD

 

[bertie]

Peter's code place a formula over a range of cells. If you want to place a different formula in each cell the only way I can think of doing so is to loop through the data.

I have changed the column references in my working sheet and it seems to work at my end. I have also added a sort routine.
Here is a link to my worksheet.
https://docs.google.com/file/d/0B5BFC6zPG_P0QzhzclVQX0RpeE0/edit?usp=sharing

The code will go into a standard module.

Sub Main()
   ProcessDummyList
   SortData
End Sub




Private Sub ProcessDummyList()
   Dim rng As Range
   Dim myList As String
   Dim i As Long
   Dim arr As Variant
   
   myList = InputBox("Enter a list separated by commas (,)")
   If myList = "" Then
      MsgBox "Nothing entered!"
      Exit Sub
   End If
   
   arr = Split(myList, ",")
   
   Set rng = Sheets("Sheet1").Range("C2")
   Do Until rng = ""
      For i = LBound(arr) To UBound(arr)
         If InStr(1, rng.Value, Trim(arr(i)), vbTextCompare) Then
            Sheets("Sheet1").Range("AD" & rng.Row).Value = i + 1
         End If
      Next
      Set rng = rng.Offset(1, 0)
   Loop


   Set rng = Nothing
End Sub


Private Sub SortData()
   Sheets("Sheet1").UsedRange.Select
   Selection.Sort Key1:=Range("AD1"), Order1:=xlAscending, Key2:=Range("C1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
End Sub

 

[Dazzawm]

I have looked at your sample sheet and it has nothing in AD? Thats where the words will be that need sorting like coils, leads etc.. Column C will just be a normal A-Z sort then it sorts column AD by the words.

 

[bertie]

This is sample data to show you how the code works. Did you run the code? Does it do what you want wrt "Leads" and "Coils"?

If so, then you need to edit the code to meet your needs.
Press F8 to step through the code.
The column you are going to loop through is the column containing the text "Leads", "Coils", etc.
Edit the column in this line if necessary;

Set rng = Sheets("Sheet1").Range("C2")

The dummy sort variable is output to an empty column.
Edit this line if necessary:

Sheets("Sheet1").Range("AD" & rng.Row).Value = i + 1

Edit the order of the SortData() procedure if necessary:
NB: in this example, AD contains the dummy sort variables.

Private Sub SortData()
   Sheets("Sheet1").UsedRange.Select
   Selection.Sort _
      Key1:=Range("AD1"), Order1:=xlAscending, _
      Key2:=Range("C1"), Order2:=xlAscending, _
      Header:=xlGuess, _
      OrderCustom:=1, _
      MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub

 

[Dazzawm]

This is really confusing me now. Whats all this dummy data? All columns have data in so I dont have any dummy data. The sort needs to do column C first A-Z this is a must. Then it needs to sort column AD, the the code needs to ask me 'what order do you want the words sorted' in the box. I appreciate all you are doing but I have told you the columns, so I shouldn't have to edit any of the code which is confusing me.

Thanks.

 

[bertie]

Did you even attempt to follow the instructions in post #35?

I have made the adjustments you outlined in your last post. It is working at my end.
Make a copy of your workbook and try running the code.

Option Explicit


Sub Main()
   ProcessDummyList
   SortData
End Sub


Private Sub ProcessDummyList()
   Dim rng As Range
   Dim myList As String
   Dim i As Long
   Dim arr As Variant
   
   myList = InputBox("Enter a list separated by commas (,)")
   If myList = "" Then
      MsgBox "Nothing entered!"
      Exit Sub
   End If
   
   arr = Split(myList, ",")
   
   Set rng = Sheets("Sheet1").Range("AD2")
   Do Until rng = ""
      For i = LBound(arr) To UBound(arr)
         If InStr(1, rng.Value, Trim(arr(i)), vbTextCompare) Then
            Sheets("Sheet1").Range("AL" & rng.Row).Value = i + 1
         End If
      Next
      Set rng = rng.Offset(1, 0)
   Loop


   Set rng = Nothing
End Sub


Private Sub SortData()
   Sheets("Sheet1").UsedRange.Select
   Selection.Sort _
      Key1:=Range("C1"), Order1:=xlAscending, _
      Key2:=Range("AD1"), Order2:=xlAscending, _
      Header:=xlGuess, _
      OrderCustom:=1, _
      MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub

 

[Dazzawm]

I cant get it to work, column AD wont go in the order I enter the words in the box and also the headers disappear. I don't want to take up anymore of your time. Thanks anyway.

 

[bertie]

Final thoughts:
Not sorting in the correct order?
Try reversing the order you enter the values into the InputBox.


Header Disappearing - In the SortData() procedure, change highlighted to xlYes.

Header:=xlGuess, _