Today, in yet another 'Almost Live' Dueling Excel with Mike 'ExcelisFun' girvin and Bill 'MrExcel' Jelen, Mike and Bill offer solutions to: "How can I Remove Duplicates IF the first two words -before the first two commas- are the same?
Now...Bill doesn't believe that this task can be completed with a Formula and Mike says that it can. What is your synopsis? Following along with Episode #1532 to see how to remove those dupes.
Dueling Excel Podcast #101...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Now...Bill doesn't believe that this task can be completed with a Formula and Mike says that it can. What is your synopsis? Following along with Episode #1532 to see how to remove those dupes.
Dueling Excel Podcast #101...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Live from Highline Community College is another Dueling Excel podcast, I'm Bill Jelen from MrExcel, Mike from Excel Is Fun. This question sent in by Tina. Over there, Tina.
Tina has this data and she is trying to use Remove Duplicates but only wants to remove it if the first two elements between the commas are duplicate. So red,upper,red,upper. This one goes away. Yellow, down – that one’s unique. This one goes away.
Mike has a formula. Mike is a smart guy. Mike has a formula to solve this. Should you go first?
Mike: I have a ridiculous formula.
Bill: Yeah.
Mike: I’m hoping you have some sweet and easy way.
Bill: I got to see that formula first because I can’t imagine any formula that’s going to do that.
Mike: Yeah, but I – Bill: It can’t be done.
Mike: No, no, no.
Bill: There’s no formula that will do that.
Mike: Except where I learned the concepts from this place called the MrExcel message board.
Bill: Which is a great place. There’s a lot of smart people there. Alright, I know there’s no formula. This is pointless. We’re wasting our time, Mike, there’s no formula that will solve this.
Mike: Except for uhh, I’m sure you have an easy formula there. Alright, and when I looked at this, the only way – And I’ve seen this problem before and even posted in MrExcel message board. If I have to get a second column, a comma – I don’t know how to do that except for the insert a different character there. So, how about if I’m going to use the =SUBSTITUTE. SUBSTITUTE, I’m going to take this text, comma, the old text – that’s what I want to find, comma. The new text I want to insert is this little bar, and I want the instance is 2.
Bill: Oh, sweet. Oh, oh, ohhh. Oh, look at that. Look at that. That’s hot. Okay, go ahead.
Mike: Alright. So… Bill: Oh, that’s cool.
Mike: Now, once I have that, I’m going to have to use this twice because now I can say, Find that character and take the leftmost.
So I can go Copy this, and I’m going to find the position first with SEARCH. Notice I just copied that, SUBSTITUTE. I’m going to say, =SEARCH. I always use SEARCH instead of FIND if it’s not case sensitive because all you have to do is hit S+Tab. Find that text within there. Right, that gives me the position. I want to subtract 1 actually. And then I’m going to say the left. Now, I told you this is going to get ridiculous. The left of what? Ctrl+V, that substitute thing, comma and that’s how many characters from the left. Alright, so now I have that and I can say on that column, =COUNTIF and I’m going to take that range, colon, comma, left arrow, close parenthesis. I’m going to make expandable range so F4. So as I copy this down, it’ll be counting. Any time it’s greater than 1, I want to say True, so I’m going to change this to a logical formula.
Now, that’s kind of ridiculous. I have to put Text2 and then True slash False, right?
With some headers and then Sort and Delete.
I actually could probably make a single cell formula but that sort – Bill: What – Wait. So go back in the True and False. Do you have – You don’t have Filter By Selection, your Quick Access Toolbar?
Why don’t you just Filter By Selection to all the Trues. So right here, right? Right-click, Filter, Filter to Selected Cells Value and then delete all thing. Just delete those rows.
That is Delete. Because it only will delete the visible ones, right?
Mike: Ahh, you mean like this?
Bill: Why I hope so.
Mike: Ahhh, I know – I think that the way I would do it is – I forgot the keyboard trick for – Bill: Alt+E D R. Alt+E D R Mike: So, it’s uhh – like – Bill: But, not the heading.
Mike: There is a keyboard shortcut for Visible Cells Only at the bottom.
Bill: You don’t have to do that, though.
Just try it. We’ll undo it if it doesn’t work.
Mike: Alt, what?
Bill: So here, do this. Select this, Alt+E D R. Alt+E D R, Yes, and then turn off the filter, just getting back the data.
Mike: Ctrl+Shift+Del?
Bill: Huh! There we go and everything that’s left – so- Mike: Absolutely beautiful.
Bill: When you filter and delete, it doesn’t delete the hidden rows.
Mike: That is amazing.
Bill: Alright, now wait. Okay, so that formula makes my head spin. Like all of Mike’s formulas make my head spin. Now I think Tina comes here with this problem. And I say, “Tina, I’m going to have to knock out a really hard formula here. I need absolute silence so you need to go away. Go downstairs and buy me a Coke. And by the time you get back, I’ll have this formula working.” And then I wail. Tina’s not here watching me do this.
I will do Alt+D E, Text to Columns, Delimited by a Comma, click Finish. Undo, Alt+D E, Delimited by a Comma. In Step 3 though, I wanted to go to column B, alright? So, I’m going to make all those words split out over there.
And then I’m going to have my little Head 1, heading 1. I’m going to grab that Fill Handle and drag across and the amazing thing about Remove Duplicates – so Ctrl+* Remove Duplicates out here is pretty hot. Most people don’t realize that it will look at two columns.
I’m going to Unselect All, choose Head 1, Head 2, click OK. And it got rid of the ones who are both Heading 1, concatenate it with Heading 2 was the same as something else.
And at that point, I can just delete all the stuff out here and – Mike: Is that not totally awesome!
Bill: Can’t see, the sweat on my brow. When Tina comes back, I say that was the hardest formula. You wouldn’t believe I had to go to Mike to figure out what the answer was.
Mike: And I had to go to MrExcel Bill to get the easy way.
Bill: This is cool. This is wild. But you know, Mike, I have to thank Mike. Mike comes up with most of these problems with the Dueling Excel Podcast. I think I've come up with 2 out of 102. But we rolled in here today and he thought I was going to come up with hard stuff and that's the great thing. Everyone has a different way to solve things in Excel.
Mike: Woohoo! Absolutely beautiful.
Bill: Well, hey, that was our – Was that 101? 102?
Mike: 102.
Bill: 102. Episode 102.
Mike: It’s already 1 or 2 in the morning.
Bill: Yeah, it feels like it. Actually, yeah.
Well hey, I want to thank everyone for stopping by. We’ll see you next time for another net- Dueling Excel Podcast from MrExcel and… Mike: Excel Is Fun.
Bill: Alright.
Tina has this data and she is trying to use Remove Duplicates but only wants to remove it if the first two elements between the commas are duplicate. So red,upper,red,upper. This one goes away. Yellow, down – that one’s unique. This one goes away.
Mike has a formula. Mike is a smart guy. Mike has a formula to solve this. Should you go first?
Mike: I have a ridiculous formula.
Bill: Yeah.
Mike: I’m hoping you have some sweet and easy way.
Bill: I got to see that formula first because I can’t imagine any formula that’s going to do that.
Mike: Yeah, but I – Bill: It can’t be done.
Mike: No, no, no.
Bill: There’s no formula that will do that.
Mike: Except where I learned the concepts from this place called the MrExcel message board.
Bill: Which is a great place. There’s a lot of smart people there. Alright, I know there’s no formula. This is pointless. We’re wasting our time, Mike, there’s no formula that will solve this.
Mike: Except for uhh, I’m sure you have an easy formula there. Alright, and when I looked at this, the only way – And I’ve seen this problem before and even posted in MrExcel message board. If I have to get a second column, a comma – I don’t know how to do that except for the insert a different character there. So, how about if I’m going to use the =SUBSTITUTE. SUBSTITUTE, I’m going to take this text, comma, the old text – that’s what I want to find, comma. The new text I want to insert is this little bar, and I want the instance is 2.
Bill: Oh, sweet. Oh, oh, ohhh. Oh, look at that. Look at that. That’s hot. Okay, go ahead.
Mike: Alright. So… Bill: Oh, that’s cool.
Mike: Now, once I have that, I’m going to have to use this twice because now I can say, Find that character and take the leftmost.
So I can go Copy this, and I’m going to find the position first with SEARCH. Notice I just copied that, SUBSTITUTE. I’m going to say, =SEARCH. I always use SEARCH instead of FIND if it’s not case sensitive because all you have to do is hit S+Tab. Find that text within there. Right, that gives me the position. I want to subtract 1 actually. And then I’m going to say the left. Now, I told you this is going to get ridiculous. The left of what? Ctrl+V, that substitute thing, comma and that’s how many characters from the left. Alright, so now I have that and I can say on that column, =COUNTIF and I’m going to take that range, colon, comma, left arrow, close parenthesis. I’m going to make expandable range so F4. So as I copy this down, it’ll be counting. Any time it’s greater than 1, I want to say True, so I’m going to change this to a logical formula.
Now, that’s kind of ridiculous. I have to put Text2 and then True slash False, right?
With some headers and then Sort and Delete.
I actually could probably make a single cell formula but that sort – Bill: What – Wait. So go back in the True and False. Do you have – You don’t have Filter By Selection, your Quick Access Toolbar?
Why don’t you just Filter By Selection to all the Trues. So right here, right? Right-click, Filter, Filter to Selected Cells Value and then delete all thing. Just delete those rows.
That is Delete. Because it only will delete the visible ones, right?
Mike: Ahh, you mean like this?
Bill: Why I hope so.
Mike: Ahhh, I know – I think that the way I would do it is – I forgot the keyboard trick for – Bill: Alt+E D R. Alt+E D R Mike: So, it’s uhh – like – Bill: But, not the heading.
Mike: There is a keyboard shortcut for Visible Cells Only at the bottom.
Bill: You don’t have to do that, though.
Just try it. We’ll undo it if it doesn’t work.
Mike: Alt, what?
Bill: So here, do this. Select this, Alt+E D R. Alt+E D R, Yes, and then turn off the filter, just getting back the data.
Mike: Ctrl+Shift+Del?
Bill: Huh! There we go and everything that’s left – so- Mike: Absolutely beautiful.
Bill: When you filter and delete, it doesn’t delete the hidden rows.
Mike: That is amazing.
Bill: Alright, now wait. Okay, so that formula makes my head spin. Like all of Mike’s formulas make my head spin. Now I think Tina comes here with this problem. And I say, “Tina, I’m going to have to knock out a really hard formula here. I need absolute silence so you need to go away. Go downstairs and buy me a Coke. And by the time you get back, I’ll have this formula working.” And then I wail. Tina’s not here watching me do this.
I will do Alt+D E, Text to Columns, Delimited by a Comma, click Finish. Undo, Alt+D E, Delimited by a Comma. In Step 3 though, I wanted to go to column B, alright? So, I’m going to make all those words split out over there.
And then I’m going to have my little Head 1, heading 1. I’m going to grab that Fill Handle and drag across and the amazing thing about Remove Duplicates – so Ctrl+* Remove Duplicates out here is pretty hot. Most people don’t realize that it will look at two columns.
I’m going to Unselect All, choose Head 1, Head 2, click OK. And it got rid of the ones who are both Heading 1, concatenate it with Heading 2 was the same as something else.
And at that point, I can just delete all the stuff out here and – Mike: Is that not totally awesome!
Bill: Can’t see, the sweat on my brow. When Tina comes back, I say that was the hardest formula. You wouldn’t believe I had to go to Mike to figure out what the answer was.
Mike: And I had to go to MrExcel Bill to get the easy way.
Bill: This is cool. This is wild. But you know, Mike, I have to thank Mike. Mike comes up with most of these problems with the Dueling Excel Podcast. I think I've come up with 2 out of 102. But we rolled in here today and he thought I was going to come up with hard stuff and that's the great thing. Everyone has a different way to solve things in Excel.
Mike: Woohoo! Absolutely beautiful.
Bill: Well, hey, that was our – Was that 101? 102?
Mike: 102.
Bill: 102. Episode 102.
Mike: It’s already 1 or 2 in the morning.
Bill: Yeah, it feels like it. Actually, yeah.
Well hey, I want to thank everyone for stopping by. We’ll see you next time for another net- Dueling Excel Podcast from MrExcel and… Mike: Excel Is Fun.
Bill: Alright.
