How to sort words in a cell in alphabetical order?

[Karthik]

Hi,


I would like to sort the words in a particular in alphabetical order. For instance, I've a column full of phrases such as 'skechers womens clothing,' 'skechers clothing womens,' 'discount apparel sales,' 'apparel sales discount,' etc. Basically, I want to identify the duplicates. In the eg., I gave 'skechers womens clothing' is technically a duplicate of 'skechers clothing womens,' but Conditional formatting or VLOOKUP will not help me establish that fact. So, I'm planning to sort them in alphabetical order to highlight them as duplicates. Can you help me here with a formula or VBA code?

 

[NARAYANK991]

Hi Karthik ,


Thanks for an interesting problem.


Can you confirm whether the number of keywords will always be 3 or will it vary over a wide range ?


Narayan

 

[NARAYANK991]

Hi Karthik ,


If the number of keywords will always be 3 , then using several columns of formulae gives the desired result.


1. Find the first and second spaces in the string e.g. in "skechers womens clothing" , the first space occurs in position 9 , and the second in position 16.


2. Based on 1 , isolate the individual words in 3 columns e.g. in the above example , "skechers" appears in one column , "womens" in another , and "clothing" in a third.


3. Using IF statements , get the word which appears first/last in alphabetical order.


3. Using a MATCH statement , get the position of this word in the horizontal array of 3 words e.g. "womens" will be the last word and its position will be 2.


4. Again using IF statements , and using the result of step 3 above , compare the remaining two words to get the next word in ascending / descending alphabetical order.


5. Repeat step 3 to get the position of this second word in the original horizontal array e.g. "skechers" will be in position 1.


6. Use IF statements to isolate the last remaining word.


Irrespective of how the individual words are ordered in the original string , this will always give you "womens skechers clothing". You can use this reordered string to search for duplicates.


Narayan

 

[Jack]

Karthik I have a non VBA approach you could take.


Text to columns will split your words up into words pretty easily using fixed width. I don't know how many possible words you might have but enter this formula to the right leaving enough columns for max possible words. I have assumed the list of text starts at a and 7 words B to H is the max words you have and where the text to columns can occur.


Maybe this bit you could skip but you could first use this formula to see the max words from each line - enter it somewhere to say I2.


=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1


Then in J copy across this formula below 7 columns . It will indicate if each line has a duplicate.


=IF(COUNTIF($B$1:$H$1000,B2)>1,"Duplicate!","")


However with words like womens and clothing of course it will throw up lots of duplicate . So you could identify and exclude certain unnecessary words by enhancing this formula with OR()


=IF(AND(COUNTIF($B$1:$H$1000,B2)>1,B2<>"womens",B2 <> "clothing"),"Duplicate!","")

 

[Jack]

OK this task has been bugging me and here is a simpler solution I think.


Firstly keep your original data intact. If it's in Col A copy to say starting C2 do the text to columns bit as above - if your words are only separated by a space this will work great. Now you have x columns hopefully. You will need to amend the following macro as I have assumed 3 columns of words and haven't got time to solve how to determine the last column of a range. Maybe someone else knows this bit (tricky I think). Then put this module into VB Editor and run it or attach to a CommandButton. Make very sure you are in the right worksheet before triggering it as I haven't put in much in way of checks that you are in the right sheet - change your tab to say TexttoColsData so below macro will check you are in this at least before going off. If you have thousands of lines this will take some time and I haven't put in any code to > speed as you might only have 100 lines.


Once the macro has been done a normal sort will then clearly show the duplicates.


Cheers


Sub SortHorizontally()

'

' SortHorizontally Macro

' Macro recorded 18/10/2011 by john

'

Dim start As Range

'Dim Lastcol As Range WIP

'Does anyone know how to determine the last column in VBA?


worksheets("TexttoColsData").select 'assuming you changed the worksheet name


Range("c2").Select 'you need to amend to suit but first cell of your columns of data


Do

Range(ActiveCell, ActiveCell.Offset(0, 3)).Select 'You need to amend to Max of columns you have if not 3

Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _

DataOption1:=xlSortNormal

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell = ""

End Sub

 

[Jack]

By the way in case it is confusing anyone this line:


Dim start As Range


is not needed, as I just decided to do the quick and dirty range("c2").


No more, from me, but I enjoyed this problem and I think it's a common issue. I often work with thousands of lines of a patient base and our well meaning staff are not always consistent with entry.

 

[Jack]

Hi me again, regarding how to adjust above code to dynamically select the right number of columns in case the no of words in the string is variable.


John Walkenabch has a wonderful Range Selection Demo: http://spreadsheetpage.com/index.php/file/range_selection_demo


So below now works for any no. of columns of words and still just have to change C2 to your own starting point.


Sub SortHorizontally()

'

' SortHorizontally Macro

' Macro written 18/10/2011 by john

'


Worksheets("TexttoColsData").Select 'assuming you changed the worksheet name

Range("c2").Select 'amend to suit but first cell of your columns of data


Do

Range(ActiveCell, ActiveCell.End(xlToRight)).Select

Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _

DataOption1:=xlSortNormal

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell = ""

End Sub