VBA Descrambling Words

[Stephen_IV]

Good morning. I have a list of Presidents that I have have on Sheet2 i.e.
WASHINGTON
LINCOLN
KENNEDY

On sheet1 I have scrambled the presidents i.e for WASHINGTON I could have TNSHWOINGA or GNOWNIHSA or WISOTGHNNA exc. Is there a way to descramble the the words on Sheet1 with a function that will match the list on Sheet2? So for instance if I had TNSHWOINGA it would bring back WASHINGTON. All of the names will be distinct. Thanks in advance

 

[L. Howard]

I have a worksheet that uses a common permutation code, a spellcheck code, and sort code to unscramble words.

Huge drawback is that it iterates each combination of the scrambled letters with the following length/combinations.

3 - 6
4 - 24
5 - 120
6 - 720
7 - 5040
8 - 40320
9 - 362880
10 - BUST over one million

So Taft, Bush, Davis scrambles are almost blink fast. Jefferson is a "few" minutes, maybe not that long, and Washington will not play because it is over a million rows.
The permutation goes fairly fast, but the spell check labors along, then a quick sort to rid all misspelled combinations.
Not sure if there are other codes that will do the job any faster or able to handle over a million rows.

Howard

 

[Eric W]

I also have a program that does permutations and sends the permutations to the Excel Spell Checker. But as Howard said, the number of permutations grows rapidly, and if the words aren't in the spell checker, like proper names, then it won't work.

So I took a different approach. If you already have a list of the unscrambled words, and it's just a matter of finding the matching word, then we can do it with a "simple" formula.

For example:

	A	B	C
Words	Match
TNSHWOINGA	WASHINGTON
GNOWNIHSA
WISOTGHNNA	WASHINGTON
COLNLIN	LINCOLN
colnlin	LINCOLN
dorf	Ford
jackons	Jackson

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=LOOKUP(2,1/(MMULT((LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),CHAR(COLUMN(INDIRECT("A:Z"))+64),""))=LEN(Sheet2!$A$2:$A$10)-LEN(SUBSTITUTE(UPPER(Sheet2!$A$2:$A$10),CHAR(COLUMN(INDIRECT("A:Z"))+64),"")))+0,ROW(INDIRECT("1:26"))^0)=26),Sheet2!$A$2:$A$10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The way it works is by compiling a list of how many As, Bs, Cs, etc. are in the scrambled word. Then finding out how many As, Bs, Cs, etc. are in each of the reference words. If we get a match where every letter has the same count, then we've found the word.

 

[Stephen_IV]

Eric,

Thank you very much! That seems to do the job and is exactly what I am looking for! I appreciate your health and time! Thanks again!

 

[Eric W]

Glad it works for you! That one was kinda fun to figure out!

 

[L. Howard]

@ Eric

Pretty darn slick!!

Maybe obvious to others, but I note the lists scrambled vs unscrambled do not have to correspond row to row, i.e. DORF as second entry scrambled list will find FORD as sixth entry on unscrambled list.

Thanks for the formula.

Howard