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?

 

[Peter_SSs]

Thanks Peter, how does this work?

Step through the code a line at a time (F8) and keep an eye on the column immediately to the right of the last column of your data.

What about my post 18 where I need the word coil incorporated into it?

Sorry, I missed that. Just change the formula line

.Formula = "=IF(COUNTIF(AD1,""*Leads*""),1,IF(COUNTIF(AD1,""*Coil*""),2,""""))"

 

[Dazzawm]

Thanks Peter, works great. Out of curiosity is it possible to have an input box so I can use this code on all different files to do the following:-

It will always have to do the sort column AD where the words will be first then column C, A-Z.

1. How many conditions do you need (at the moment it is 2 for leads then coils, could be more)?

2. What are the words (I enter the words in the input box - leads and coil in order of what I want first in the sort.)

 

[Peter_SSs]

That's a bit more than I am up for here I'm afraid.

 

[Dazzawm]

Thanks Peter, works great. Out of curiosity is it possible to have an input box so I can use this code on all different files to do the following:-

It will always have to do the sort column AD where the words will be first then column C, A-Z.

1. How many conditions do you need (at the moment it is 2 for leads then coils, could be more)?

2. What are the words (I enter the words in the input box - leads and coil in order of what I want first in the sort.)

I did get the sort criteria incorrect it should be by column C first then column AD

 

[Dazzawm]

If anyone can help this is a recap of what I need.

1. The sort will always sort column C A-Z first.

2. Then I need an input box so I can enter the words in priority in column AD. These words can be anywhere amongst other words in the cell which is why I can't use the normal search function.

There will be headers in row 1 so the sort needs to incorporate all other columns maybe up to column BA. These headers will determine all the columns in the range.

 

[Dazzawm]

An example:-

Sheet1

*	C	AD
1	Before Sort	*
2	Test2	Front Leads One
3	Test1	Front Leads
4	Test1	Rear Test
5	Test2	Front Coil Two
6	Test1	Front Test
7	Test2	Rear Test
8	Test1	Front Coil
9	Test2	Front Test

<TBODY>
</TBODY>

I run the code and input box appears asking what the words do I want to sort in order. I would enter in this example Leads, Coil, Rear. The result would be

Sheet1

*	C	AD
11	After Sort	*
12	Test1	Front Leads One
13	Test1	Front Coil
14	Test1	Rear Test
15	Test1	Front Test
16	Test2	Front Leads
17	Test2	Front Coil Two
18	Test2	Rear Test
19	Test2	Front Test

<TBODY>
</TBODY>

My objective is to put this in my Personal Macro Workbook and use on dozens of different files and obviously change the words to suit.

Thanks

 

[bertie]

Hi Dazz,

Thanks for the pm.

I had a look at your thread and you have a lot of experienced people offering help. I haven't recreated your workbook or tried any of the proposed solutions. Having skimmed through the three pages of replies, it looks like you have hit a stumbling block at posts #22 #23, how to incorporate a custom list in your sort.

See if this article helps.
http://blog.contextures.com/archives...-custom-lists/

If this matches your needs, record a macro as you go over the steps.
Then, in the generated code, replace the list on the spreadsheet with the contents of your InputBox.

Bertie

 

[Dazzawm]

Is what I am asking not possible to do with a macro? Am I asking too much? It seems the stumbling block is the text I want to sort can be anywhere in the cells and not at the beginning, hence not being able to do a normal sort A-Z.

 

[bertie]

I have had an oportunity to look over the thread and by post #15 you appear to have found a solution using Ben an Jonmo responses.


In post #18 you want to use this approach to cater for more than one word.


Using the same approach as Ben and Jonmo, the macro below will:
Prompt the user for a list of words separated by a comma.
Split the input into an array.
Loop through Sheet1-columnA in sample data
inner loop to process the array, use INSTR to find match
output index to column AL if match found


See sample data for result:

Excel 2007
	A	AL
1	List	Dummy List
2	Leads	1
3	xx
4	xxx
5	no leads	1
6	xxx
7	coils	2
8	no coils	2
9	xxx
10	xxx
Sheet1

Test this in a new worksheet first to see if it meets your needs.
Edit SheetNames and Ranges where necessary.

Sub Main()
   'process dummy lis
   'sort the data
End Sub




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("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


   Set rng = Nothing
End Sub

 

[Dazzawm]

Thanks for your help and time bertie. I did as you said but nothing happened. I changed A2 to C2 and AL to AD in the code. I ran the code and entered the words in order that I wanted them sorted but nothing happened.

Peters code did as I need, I just need an option of an input box to ask me the words incorporated into that really.