Finding anagrams in a word list

[Hitch75]

Hi guys,

This one is tricky, I guess...

In column A we have words, like this:

CHAIR
EXCEPT
WORLD
STREET
OUTSIDE
PEOPLE
WINDOW
LETTER
BOOK

In column B we have another list of words:

NIGHT
DREAM
EXPECT
THOUGHT
TEDIOUS
OFFICE
SETTER
LIBRARY
BEFORE
OFFER

I need a formula in C1 which will check all the words from column B and find the ones which are anagrams of some of the words from column A, and then return those words next to them.

(Anagram is the word made up of another word's letters, without repeating or omitting any, for example anagram of SPARK is PARKS).

In the example above, we would have the words: EXCEPT next to EXPECT (in C3), OUTSIDE next to TEDIOUS (C5), and STREET next to SETTER (C7).

Any ideas?
Thanks!!

[windknife]

I don't know what I do ! Maybe it's work!
I try to merge two to one, but I can't do it, so I use two column to work it out.

Try this,
C1

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter
D1

Please Login or Register  to view this content.

copied both down.

You can see attached.
Hope this helps,
WindKnife

[shg]

this UDF returns the characters of the input string in alphabetical order. You could use it in an adjacent column, and the sort by the result.

Please Login or Register  to view this content.

Example usage: = AnaSort(A1)

[Hitch75]

@windknife:
when i try the same formulas in other files with more words, it doesn't work anymore (i don't understand what are these words that i get...)

@shg:
this looks great, but i don't know what to do with this??

thanks to both!

[Hitch75]

@shg: here's a file with other wordlists and the same formulas.

[windknife]

Could you put your testing workbook ?
WindKnife

[windknife]

I think shg's VBA is more better than me about performance issue.
If you want use formula, I modified original formula as follows.

C1

Please Login or Register  to view this content.

Committed with Ctrl+Shift+Enter.
Remember to modified $A$200 to over your data in the future.
You can also see attached.
Hope this helps,
Windknife

[Hitch75]

wildknife, sorry, your formula doesn't work...
or maybe you didn't understand me...
in your file, the word MALA (D17) comes next to ALLA (B17), which is not an anagram.

can you help me with the VBA thing, i have no idea what it is and how to use it?

[windknife]

Its my fault, I don't consider duplication letter.
By the way, based on Shg's function you can use following VBA.

Please Login or Register  to view this content.

You can also see attachment more detail.
Hope this helps,
WindKnife

[Hitch75]

This one works!
Thanks guys!!!

[Hitch75]

One more question, for WindKnife:

Would it be possible to make it avoid listing the words which are absolutely the same (with the same order of letters)?

For example, if the program winds the word STAR in column A, it will list it next to the word STAR in column B, but I want it to list only RATS and ARTS...