# Advanced Formula Challenge #2: Identifying Anagrams 10

For this, the 2nd in the series of Advanced Formula Challenges, readers are asked to come up with a solution to the following: Given two lists of names in B1:B10 and E1:E10 (as above), a formula is to be entered into A1, such that, when copied down to A10, returns TRUE if, for the corresponding name in column B, there exists at least one name in the range E1:E10 which is an anagram of that name.

Solutions should work not just for the data present here, but given any names of between 3 and 10 characters in the two ranges. It can be assumed that no cell in either of these ranges is empty.

Best of luck! Solutions next Sunday.

1. Bill says:

Brute force method but works :-))

`=ISNUMBER(MATCH(REPT(1,LEN(B1)),IF(LEN(B1)=LEN(\$E\$1:\$E\$10),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(\$E\$1:\$E\$10),LOWER(MID(B1,1,1)),1,1),MID(B1,2,1),1,1),MID(B1,3,1),1,1),MID(B1,4,1),1,1),MID(B1,5,1),1,1),MID(B1,6,1),1,1),MID(B1,7,1),1,1),MID(B1,8,1),1,1),MID(B1,9,1),1,1),MID(B1,10,1),1,1),""),0))`

Greetings

2. Ouch! 🙂 I knew I shouldn’t have put a restriction on the number of characters! Let’s see you adapt that for up to 100 characters, then! 🙂 …Wait, no, don’t – you actually would as well! 🙂

What can I say? It may be slightly against the “ethos” of this site, but in the end I can’t argue with a correct solution, brute force or no brute force!

Well done, Bill! And good to see you here, by the way!

3. Ben Schwartz says:

This is the best I could come up with, even with borrowed construction. It works with your specific example, however, it will fail when there are values on each side with all of the same letters but a different duplicate. For example State and Sates.

I can’t take credit for it. I pretty much merged two formulas, one from Wildknife on the Excelforum.com site, and one from Aladin Akyurek on the Mrexcel site. Also confirmed with Control+Shift+Enter:

`=IFERROR(LEN(B1)=LEN(INDEX(\$E\$1:\$E\$10,MATCH(TRUE,MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(MID(B1,ROW(\$1:\$26),1)),\$E\$1:\$E\$10))*(LEN(\$E\$1:\$E\$10)=LEN(B1)),1,0),ROW(\$1:\$26)^0)=26,0)))*(SUM(LN(CODE(LOWER(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)))))=SUM(LN(CODE(LOWER(MID(INDEX(\$E\$1:\$E\$10,MATCH(TRUE,MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(MID(B1,ROW(\$1:\$26),1)),\$E\$1:\$E\$10))*(LEN(\$E\$1:\$E\$10)=LEN(B1)),1,0),ROW(\$1:\$26)^0)=26,0)),ROW(INDIRECT("1:"&LEN(INDEX(\$E\$1:\$E\$10,MATCH(TRUE,MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(MID(B1,ROW(\$1:\$26),1)),\$E\$1:\$E\$10))*(LEN(\$E\$1:\$E\$10)=LEN(B1)),1,0),ROW(\$1:\$26)^0)=26,0))))),1)))))),FALSE)`

Very difficult challenge. I tried for hours but couldn’t come up with the right solution, before I turned to the internet for assistance.

4. Thanks, Ben! And welcome to the site! 🙂

Certainly not a short one, that! And yes, more importantly, I’m getting quite a few incorrect results when testing: like you said, it won’t pick up e.g. “Adeline” if both “Delanie” (an anagram) and, say, “Adelina” (not an anagram) are in the second range.

Hope you don’t mind – I removed the links from your post. Not that I don’t want people to see the content of those links, but more so because I’d like people to have a go at solving this problem without necessarily going through the attempts at those – and other – sites, if possible.

Of course, people may well choose to research this question – as you did – on the internet, and that’s fine if they want to. I personally didn’t know this question had been tackled before (I didn’t look to be honest), but given that the solutions there don’t appear to work in all cases, perhaps some fresh ideas here can hope to go one further!

I’ll perhaps add those links back in when we get to next week so that we can compare. I’ve left your citations in, of course.

Thanks again for the contribution and hope to see more of you soon!

5. Bill says:

So…..this time not brute force method :-))) And not against the “ethos” of this site ;-)))))

`=ISNUMBER(MATCH(SUM(10^(CODE(UPPER(MID(B1,TRANSPOSE(ROW(INDIRECT("1:"&LEN(B1)))),1)))-64)),MMULT(IF(IF(LEN(B1)=LEN(\$E\$1:\$E\$10),MID(\$E\$1:\$E\$10,TRANSPOSE(ROW(INDIRECT("1:"&MAX(LEN(\$E\$1:\$E\$10))))),1),"")<>"",10^(CODE(UPPER(MID(\$E\$1:\$E\$10,TRANSPOSE(ROW(INDIRECT("1:"&MAX(LEN(\$E\$1:\$E\$10))))),1)))-64),0),ROW(INDIRECT("1:"&MAX(LEN(\$E\$1:\$E\$10))))^0),0))`

Greetings 🙂

6. Excellent stuff, Bill! Can’t argue with that! And yes, much happier to see you’ve dropped your “chainsaw” approach to the problem. 🙂

I used a similar approach but managed to come in at 60 characters less ( 🙂 ) which I’ll share with everyone next week. Of course, someone may well come along with an even better (and shorter) solution before then. We’ll see.

Well done again – great solution!

7. Bill says:

I am looking at my previous post and I see that first TRANSPOSE is not needed.

I have another solution of this problem but without Excel formula. Power Query can do that ( with UDF function – M language). Benefits – we can get all anagrams (not only first one) connected to the first list of names.

If you are interested in i can send you my file with this method.

Greetings

8. Thanks a lot. I appreciate the offer, Bill. Unfortunately I want this site to stay (at least for the moment) as an Excel-worksheet-formulas alone site. Like I said in my reply to your comment on the other post, I actually think there’s a lot more that worksheet formulas can achieve than most people might realise.

For this challenge, for example, I’ve now got the solution down to a “mere” 268 characters, which, when you see it tomorrow I hope you’ll agree is not actually that difficult (once you understand the concepts), not is it unnecessarily complex, in my opinion.

Still, someone else could yet come along between now and then with an even simpler solution using worksheet formulas alone. You never know!

Regards

9. Emily says:

This is very messy but I am using helper columns to sort the strings alphabetically & then just looking for a match:

`=IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},1))),0),1)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},2))),0),2)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},3))),0),3)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},4))),0),4)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},5))),0),5)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},6))),0),6)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},7))),0),7)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},8))),0),8)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},9))),0),9)),"")&IFERROR(CHAR(LARGE(IFERROR(CODE(UPPER(MID(E1,{1,2,3,4,5,6,7,8,9,10},10))),0),10)),"")`
10. MichaelCH says:
`=OR(MMULT(N(LEN(SUBSTITUTE(UPPER(B1),CHAR(COLUMN(BM:CL)),))=LEN(SUBSTITUTE(UPPER(E\$1:E\$10),CHAR(COLUMN(BM:CL)),))),ROW(65:90)^0)=26)`

This site uses Akismet to reduce spam. Learn how your comment data is processed.