From the course: Excel Power User Challenge

Which of the pairs are anagrams? - Microsoft Excel Tutorial

From the course: Excel Power User Challenge

Start my 1-month free trial Buy for my team

Which of the pairs are anagrams?

“

- [Instructor] Let's take immediate notice of the extra brain cell that I'm providing you before we get into this challenge because you really have to know your functions. You got to have strategy, you got to have organization in order to do this. So, you're welcome. Here we have two lists and we want to know which pairs are anagrams. What's an anagram? An anagram is where you take a word or a term or phrase and scramble the letters around and get another word, term, or phrase, and none can be duplicated, left out, whatever. Look at the pair in row four. Who, W, H, O and hew, H, E, W are not anagrams. If the value in C4 was how, H, O, W, that would be an anagram with who. We can look at row seven, char and arch. It's easy to see that those are anagrams. Part of the challenge, we are going to ignore spaces and capitalization. So if we did have the pairs carpet and then per act, we would consider that an anagram in spite of the space between per and act. Okay, so go ahead, check out the data, pause the video. Is hat pen near tram an anagram of Martina Panther? Let Excel help you figure it out. (upbeat jazz music) (electronic buzzing) Right, hopefully you didn't need more than the extra brain cell that I gave you to give you a head start. Hopefully you didn't need it at all. Let's do it. I am going to start a new sheet. I don't want these grid lines. Okay, so I start in B2. All right, so I'm going to do this equals. All right. And slide over. Okay, do I have everything? All right, it goes down to section and noticed. All right, beautiful. Don't need these, delete. All right. Let's tighten things up a bit. Bring this up, okay. Let's see. Because we don't care about capitalization or spaces, equals substitute. What text? This text, comma, old text. Double quote, space, double quote. Comma, new text, double quote, double quote. Close parentheses, enter. Good. We don't have any spaces. Now I'm going to go to F2 equals mid, open parentheses and then E2, comma, sequence, double click. One, comma, len, open parentheses. And then we want E2 again. Close the len, close the sequence. Comma, one and enter. All right, I am going to accept this correction. Yes, all right. So look at this. Lobster soup, all in individual characters. Okay, go back. Now I want to wrap this in sort, open parentheses, the array. We got the array already. Comma, sort index. I don't need that. I want the by column, comma, comma. Now one, close parentheses, enter. Okay, now they are all in alphabetical order and still all spread out. Now, text join, double click. What delimiter do I want? I don't want one. So double quote, double quote, comma, ignore empty cells. I'm going to put a one here. Comma, now the text. That's what we already have. Now close parentheses and enter. Look at that. So let's double click and send this down. Good. Now the next thing that we have to do with these highlighted already, drag 'em over into the next column. Now I want to drag this over. Look at that. All right. And then double click, send that down, bring this over, right? Drag this. Now we've got this one. Double click so we can see what's what. Double click to send this down. Now let's do a test, equals, this, equals, this, equals true. Okay, I'm going to drag this one over here. All right. Got B and C. All right, double click. East clown, low stance. We already knew about who and hew. The pair in row five are not a pair because there is a character between the three and the capital L. Okay, all right. Yep, that's an anagram. Gloomiest survey, mysterious gloves. B12 has two S's. C12 has three S's. All right, so now let's go and do this officially. We wanted the answer over here so I'm going to go equals. And double click. We are done. Wow. Now, I want to say you may never do this ever in life, but these are the kind of things that help you get good at Excel. You have to stop and think strategy, organization, understand your data, understand the challenge, and then draw from what you know about Excel and then weave the relevant bits of knowledge together into a solution. Yeah, you got to play, you got to have fun and take on challenges that may not have any practical use, but they stretch you, they make you good, they make you valued.

Contents