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?

 

[Dazzawm]

Re: VBA To Do A Custom Sort

Just to reiterate the word 'Leads' will be amongst other words in the cells and could be at the beginning, middle or end.

 

[BenMiller]

Re: VBA To Do A Custom Sort

Try it. Type =--(ISNUMBER(FIND("leads",A1)) into A2.
Type test data into A1. If it contains the word leads, A2 will display 1. Otherwise, A2 will display 0.

 

[Dazzawm]

Re: VBA To Do A Custom Sort

I think it needs another ) at the end and it still doesn't work. I'm using Excel 2010, does that make a difference?

 

[Jonmo1]

Re: VBA To Do A Custom Sort

FIND is case sensitive..
So if it contains 'Leads', then it will not find 'leads'

Try changing FIND to SEARCH for it to be NON Case Sensitive.

 

[Dazzawm]

Re: VBA To Do A Custom Sort

FIND is case sensitive..
So if it contains 'Leads', then it will not find 'leads'

Try changing FIND to SEARCH for it to be NON Case Sensitive.

Thanks Jonmo that works. Is there a VBA code that will do it so I can put it in my Macro PWB and use it for various files and change it to suit?

 

[BenMiller]

FIND is case sensitive..
So if it contains 'Leads', then it will not find 'leads'

Try changing FIND to SEARCH for it to be NON Case Sensitive.

Ahh... silly, silly me! I'm typing this on my phone and I am a bit rusty in the subtle differences between formulas...

If you do want to create a helper column, it should work now. Sorry for the trouble.

 

[Dazzawm]

Thanks all, but I could really do with a macro if someone could oblige. Thanks

 

[Dazzawm]

I am using this formula to do a sort so when leads is found it puts a 1 so I then sort Z-A. How can I include another condition so that it would put a 2 for another word?

=--(ISNUMBER(SEARCH("leads",AD2)))

The word would be coil so it would look for leads and put a 1 then look for coil and put a 2 then I could do my sort.

Thanks.

 

[Peter_SSs]

Thanks all, but I could really do with a macro if someone could oblige. Thanks

Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> mySort()<br>  <SPAN style="color:#00007F">Dim</SPAN> nc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>  lr = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _<br>    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row<br>  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _<br>    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1<br>  <SPAN style="color:#00007F">With</SPAN> Cells(1, nc).Resize(lr)<br>    .Formula = "=IF(COUNTIF(AD1,""*Leads*""),ROW(),"""")"<br>    .Value = .Value<br>    Range("A1").Resize(lr, nc).Sort Key1:=Cells(2, 3), Order1:=xlAscending, _<br>      Key2:=Cells(2, nc), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _<br>      Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal<br>    .ClearContents<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Dazzawm]

Thanks Peter, how does this work? What about my post 18 where I need the word coil incorporated into it?