# VBA extract anagrams from List

#### mgirvin

##### Well-known Member
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### mgirvin

##### Well-known Member
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

##### MrExcel MVP
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

#### mgirvin

##### Well-known Member
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

##### MrExcel MVP
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
ABCDEFGHI
1anagramISOPTERANPATRONISEabcdefghibacdefghia1234567823145678aabcdefghk23145678b
2ISOPTERANYESYES
3PATRONISEYESYES
4abcdefghi  YESYES
5bacdefghi  YESYES
6a12345678    YESYES
723145678a    YESYES
8abcdefghk      YES
923145678b       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

##### Well-known Member
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

##### Well-known Member
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?

Replies
10
Views
489
Replies
15
Views
373
Replies
0
Views
484
Replies
4
Views
83
Replies
2
Views
270

1,191,682
Messages
5,987,987
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back