VBA extract anagrams from List

[mgirvin]

Dear Amazing Excel Team,
Say I had a list of words, all 9 letters long and I wanted to find all the words that had anagrams of themselves (e.g. ISOPTERAN - PATRONISE) and then list them separately as a new list.

I have tried all sorts of Excel spreadsheet solutions, but I always hit a dead end.

Can anyone write some code that I could add to a module that would accomplish this? The logic would go something like this:
Start with first word, check that word against all the words in the list (column of words), if it is an anagram with another word (e.g. ISOPTERAN - PATRONISE), then both words would be extracted and pasted in a new list for the first occurrence, if while still checking the first word in the list against the remaining words it found a second match, only the second word found would be extracted and pasted in the new list. After the first word was checked against all the words in the list the second word would be checked. Then the third, etc. until the whole list was checked.

 

[VoG]

Hi

You will find some useful anagram code here http://www.mrexcel.com/forum/showthread.php?t=253400

Hopefully you can adapt it.

 

[mgirvin]

Dear VoG II,

Thank you for that lead. Since I am not a programmer, it is hard for me to follow all the code and then adapt it - I did try, but with no luck, yet...

 

[erik.van.geit]

Dear VoG II,

Thank you for that lead. Since I am not a programmer, it is hard for me to follow all the code and then adapt it - I did try, but with no luck, yet...

Hi,

Can you explain what you are trying to adapt? Currently the code needs an input in cell A1 and will return a list of valid permutations in column B.
Did you try to make it work the way it is? That would be the first step!

kind regards,
Erik

 

[VoG]

There must be a faster way to do this because http://www.chambersharrap.co.uk/chambers/wordgames/word_wizards/wwizards.py/main returns the solutions 'instantly'.

 

[erik.van.geit]

There must be a faster way to do this because http://www.chambersharrap.co.uk/chambers/wordgames/word_wizards/wwizards.py/main returns the solutions 'instantly'.

I've been thinking a while on the tricks to get a quick response. I can only imagine some kind of database with existing words in a treestructure. But using VBA and Excel you will always need the Spelling Check which makes it slow.

 

[mgirvin]

You guys are awesome and amazing! The code did work!

I may have used my language loosely. I have not tried to adapt it by changing the code, I just look through the code and got it to run in a 9 letter word in cell A1. What I really meant by adapting was that I was trying to (wishing) that I could get it to run on a whole column of words and pull out the anagrams. But as I am not a programmer, I am not able to do this, adapting or not.

Thanks!

 

[Yogi Anand]

Dear Amazing Excel Team,
Say I had a list of words, all 9 letters long and I wanted to find all the words that had anagrams of themselves (e.g. ISOPTERAN - PATRONISE) and then list them separately as a new list.

I have tried all sorts of Excel spreadsheet solutions, but I always hit a dead end.

Can anyone write some code that I could add to a module that would accomplish this? The logic would go something like this:
Start with first word, check that word against all the words in the list (column of words), if it is an anagram with another word (e.g. ISOPTERAN - PATRONISE), then both words would be extracted and pasted in a new list for the first occurrence, if while still checking the first word in the list against the remaining words it found a second match, only the second word found would be extracted and pasted in the new list. After the first word was checked against all the words in the list the second word would be checked. Then the third, etc. until the whole list was checked.

Hi Mike Gel Girvin:

I know you asked for a VBA solution, however, let us have a look at a little play l had with a formula based approach ...

y080614h2.XLS
	A	B	C	D	E	F	G	H	I
1	anagram	ISOPTERAN	PATRONISE	abcdefghi	bacdefghi	a12345678	23145678a	abcdefghk	23145678b
2	ISOPTERAN	YES	YES
3	PATRONISE	YES	YES
4	abcdefghi			YES	YES
5	bacdefghi			YES	YES
6	a12345678					YES	YES
7	23145678a					YES	YES
8	abcdefghk							YES
9	23145678b								YES
Sheet14

array formula in cell B2 is ...
=IF(SUM(CODE(MID(B$1,ROW(INDIRECT("$1:$9")),1)))=SUM(CODE(MID($A2,ROW(INDIRECT("$1:$9")),1))),"YES","")

this is copied down and across.

As I said since you are looking for a VBA solution, this may or may not be of any interest to you.

 

[mgirvin]

Dear Yogi Anand,

Wow! Wow! Wow! Your formula is making me very happy. I spent hours coming up with almost the same formula that you did, however, I created three different formulas in three different cells and could never combine them in one cell with one array formula!

I have not tried your formula yet, but looking through it, I can tell that it works!

Brilliant! I'll go try it and post back soon!!!!

 

[mgirvin]

Dear Yogi Anand,

It worked! But I had to type it in to each cell individually. This is the same problem that I had yesterday when I was creating my much longer series of formulas.

Here's what I did. I pasted the formula in cell B2 using Ctrl + Shift + Enter, as you did, then I copied it down and over, as you said to do. But the mixed cell references acted like absolute cell references. When I check the last formula (in lower right corner), the formula was still looking at B1 and A2. What am I missing? Any ideas?