Today, in our latest Dueling Excel feature, Mike and Bill have Data to work with, but the idea behind the use of the Data becomes very odd. For Each Letter Group in a Cell, there is a designated Numeric Value for each Letter that needs to be found, applies and then the Data will rationalize. Follow along with Episode #1667 as Mike "ExcelisFun Girvin and Bill "MrExcel" Jelen sort this out and achieve the result.
Dueling Excel Podcasts...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. Slaying Excel Dragons [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! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcasts...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. Slaying Excel Dragons [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! Power Excel With MrExcel - 2017 Edition
"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: Hey welcome back, it's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I'm here with...
Mike: ExcelIsFun!
Bill: Mike Girvin, we're at Highline Community College!
You know, every week Mike comes up with these problems, and I think he does it just to try and make my head spin, holy cow, this one is insane.
So here we have this string, AGGTACCGTA, and there's a lookup table down here, A has got a value of 1, C has got a value of 1, G has- We want to add all that up.
Mike: Hey, I didn't make this up, this is straight from someone at YouTube, I think it's a scientist or something.
Bill: You're making this stuff up man, this is insane!
So, I know Mike's working on "Ctrl+Shift+Enter", this book, and he's going to come up with some array formula.
I'm just, I'm weak here, I'm going straight to VBA, Insert, Module...
Mike: Powerful you mean!
Bill: Function, this is going to be called SumByChLookup, and we're going to pass two things.
We're going to pass it the text string, so that's just MyText, comma, and then the array that we're going to look up, so we're going to call LTable, how's that.
So, we're going to start with SumByChLookup = 0.
That’s in case we don't get any hits at all.
And then we're going to loop for I = 1 to the Len(MyText).
Mike: Oh, that's cool!
Bill: And this character is x = Mid(MyText,i,1) and then we need to do a VLOOKUP now from VBA.
They don't have VLOOKUP in VBA, but they give us this great thing called Application.WorksheetFunction.
So we're going to say Y = Application.WorksheetFunction.
, and you always hold your breath right here hoping that they put it in, because not all of them have(?), but this one is in.
Yeah, alright, good.
So we're going to look up the X, the table is called LTable, that's what got passed, and its 2nd column, ,FALSE ,we're going to hardcode those little bits in.
Actually, as I think about this, I don't need to assign that, I could just kind of increment this variable as we go.
So we're going to say that is equal to that, plus this next.
That's it, when we're done, whatever is in, SumByChLookup gets passed back to Excel.
So MyText and LTable, let's give it a try here, equal.
There we go, good thing that they- that is a really hard formula to remember.
So it's that, comma, and here's our lookup table here.
I'm still going to press that 4 here, because as I copy this down, I need it to work, so 0, and copy it down.
Mike: Oh my heavens, that is amazing!
Bill: OK, now to make life even worse, whoever this person at YouTube is, and I am NOT knocking this person at YouTube, but their situation is, they need to change these numbers to the word "Zero", "Over Zero", or "Under Zero".
So I know that Mike wanted me to put a format over here, but I'm not going to do that, I'm going to go Ctrl+1 and Custom.
And the way this works is, what to do if it's positive, semicolon, what to do if it's negative, semicolon, what to do if it's 0.
So if it's positive, I want to say "Over Zero", semicolon, if it's negative, "Under Zero", semicolon, and then if it's 0, "Zero".
There we go, click OK, and all of those numbers are going to change to those words.
Even though the words, I love that, they're still numbers, so I can still add those up.
Alright, oh, that's why, you had to copy the formatting down.
But if it's just regular formatting, it will continue to work, I love that custom number format.
Alright Mike, blow me away with an array formula!
Mike: Oh yeah!
But you just blew me away with VBA, oh, that is absolutely amazing!
And then the custom number formatting!
Alright, this actually is quite a hard one.
The first problem is, we have text in a cell, so you literally need to extract each individual value, then do a lookup, get all the numbers, then add them.
So I'm going to start with each different formula element.
This first formula element will extract all the individual pieces.
Now ROW gives us all the rows, INDIRECT takes text that represents a reference, and converts it back to a reference.
So I'm going to say "1:" and join that to the length.
Hey wait a second mister, so you use the LEN function too, right?
Bill: That's right, yeah, same function!
Mike: So what that's doing is, it's creating a row reference, 1 to whatever that length is right there, and that's a text string.
If I F9 it, the INDIRECT will convert it back to a reference, and then the ROW will give us the rows 1-10.
All that does, F9, that gives me 1-10, that is going to be an array that helps us to extract using the MID.
Hey wait a second, you used the MID too, right?
So I'm going to say the MID of that, comma, and that's an array function, or a function argument array operation.
It's going to, because that array right there has 10 values, the MID function will deliver 10 values, and I'm going to say length 1.
It's looking just like that VBA, right, but a lot more complicated, F9, there we have extracted those, that's the first part.
Now we need to do lookup add-in, and since we don't want LOG, we don't- LOGo!
There's no LOGo function.
Now, because this table here is sorted, the lookup function I'm going to use is LOOKUP.
That lookup_value, remember, it's giving us a bunch of values there, so that's an alright function array operation.
So it's going to look up all those things at once, and here's the array, that's this part right there.
So now if I close parenthesis, the way a function argument array operations work, since I gave that a bunch of values, LOOKUP will spit out a bunch of values.
There they are, so now I need to add those.
Great, that's an array, so I'm going to put it inside of SUMPRODUCT.
Now that'll give me the total, so that's the same total.
We could do that but we need to lock it.
Now I'm going to highlight that little colon, and F4, Ctrl+Enter, and double click and send it down.
Now I could use that custom number formatting, but how about this.
I'm going to use the CHOOSE function, which is a lookup function, and choose between "Under Zero", "Zero", and "Over Zero", but I need 1-2-3 as the index number for CHOOSE, so I'm going to- from that use the SIGN function!
What does the SIGN do?
The SIGN function simply gives a -1 if it's under 0, 0 if it's 0, and 1 if it's positive.
So now I want to ultimately use the CHOOSE function, I need 1-2-3, so I'm going to +2.
So now I have, for the CHOOSE, index number, that's exactly what it needs.
Now the cool thing about CHOOSE is, you simply can type your lookup values right in the formula.
So I think they want "Under Zero", if only I could type fast, "Zero", that'll be 2, and then "Over Zero", and there we go!
Bill: Alright, that's cool!
I am so glad that VBA is in Excel, because I don't have to try and figure out that formula.
Mike: Well, I'm so glad that the formulas do what they do, so I don't have to figure out VBA!
Bill: Alright, everyone's happy!
Alright.
Mike: But they do look similar, MID, LOOKUP...
Bill: You know it started out similar, but then when I used VLOOKUP, you used LOOKUP, and then you had to use CHOOSE and SIGN and SUMPROUCT, all those other things, so there you go.
Alright.
Mike: WOOHOO!
Bill: Hey, I'm sure we'll be back next week with another Dueling Excel podcast from MrExcel...
Mike: And ExcelIsFun!
Bill: Alright!
I'm Bill Jelen from MrExcel, I'm here with...
Mike: ExcelIsFun!
Bill: Mike Girvin, we're at Highline Community College!
You know, every week Mike comes up with these problems, and I think he does it just to try and make my head spin, holy cow, this one is insane.
So here we have this string, AGGTACCGTA, and there's a lookup table down here, A has got a value of 1, C has got a value of 1, G has- We want to add all that up.
Mike: Hey, I didn't make this up, this is straight from someone at YouTube, I think it's a scientist or something.
Bill: You're making this stuff up man, this is insane!
So, I know Mike's working on "Ctrl+Shift+Enter", this book, and he's going to come up with some array formula.
I'm just, I'm weak here, I'm going straight to VBA, Insert, Module...
Mike: Powerful you mean!
Bill: Function, this is going to be called SumByChLookup, and we're going to pass two things.
We're going to pass it the text string, so that's just MyText, comma, and then the array that we're going to look up, so we're going to call LTable, how's that.
So, we're going to start with SumByChLookup = 0.
That’s in case we don't get any hits at all.
And then we're going to loop for I = 1 to the Len(MyText).
Mike: Oh, that's cool!
Bill: And this character is x = Mid(MyText,i,1) and then we need to do a VLOOKUP now from VBA.
They don't have VLOOKUP in VBA, but they give us this great thing called Application.WorksheetFunction.
So we're going to say Y = Application.WorksheetFunction.
, and you always hold your breath right here hoping that they put it in, because not all of them have(?), but this one is in.
Yeah, alright, good.
So we're going to look up the X, the table is called LTable, that's what got passed, and its 2nd column, ,FALSE ,we're going to hardcode those little bits in.
Actually, as I think about this, I don't need to assign that, I could just kind of increment this variable as we go.
So we're going to say that is equal to that, plus this next.
That's it, when we're done, whatever is in, SumByChLookup gets passed back to Excel.
So MyText and LTable, let's give it a try here, equal.
There we go, good thing that they- that is a really hard formula to remember.
So it's that, comma, and here's our lookup table here.
I'm still going to press that 4 here, because as I copy this down, I need it to work, so 0, and copy it down.
Mike: Oh my heavens, that is amazing!
Bill: OK, now to make life even worse, whoever this person at YouTube is, and I am NOT knocking this person at YouTube, but their situation is, they need to change these numbers to the word "Zero", "Over Zero", or "Under Zero".
So I know that Mike wanted me to put a format over here, but I'm not going to do that, I'm going to go Ctrl+1 and Custom.
And the way this works is, what to do if it's positive, semicolon, what to do if it's negative, semicolon, what to do if it's 0.
So if it's positive, I want to say "Over Zero", semicolon, if it's negative, "Under Zero", semicolon, and then if it's 0, "Zero".
There we go, click OK, and all of those numbers are going to change to those words.
Even though the words, I love that, they're still numbers, so I can still add those up.
Alright, oh, that's why, you had to copy the formatting down.
But if it's just regular formatting, it will continue to work, I love that custom number format.
Alright Mike, blow me away with an array formula!
Mike: Oh yeah!
But you just blew me away with VBA, oh, that is absolutely amazing!
And then the custom number formatting!
Alright, this actually is quite a hard one.
The first problem is, we have text in a cell, so you literally need to extract each individual value, then do a lookup, get all the numbers, then add them.
So I'm going to start with each different formula element.
This first formula element will extract all the individual pieces.
Now ROW gives us all the rows, INDIRECT takes text that represents a reference, and converts it back to a reference.
So I'm going to say "1:" and join that to the length.
Hey wait a second mister, so you use the LEN function too, right?
Bill: That's right, yeah, same function!
Mike: So what that's doing is, it's creating a row reference, 1 to whatever that length is right there, and that's a text string.
If I F9 it, the INDIRECT will convert it back to a reference, and then the ROW will give us the rows 1-10.
All that does, F9, that gives me 1-10, that is going to be an array that helps us to extract using the MID.
Hey wait a second, you used the MID too, right?
So I'm going to say the MID of that, comma, and that's an array function, or a function argument array operation.
It's going to, because that array right there has 10 values, the MID function will deliver 10 values, and I'm going to say length 1.
It's looking just like that VBA, right, but a lot more complicated, F9, there we have extracted those, that's the first part.
Now we need to do lookup add-in, and since we don't want LOG, we don't- LOGo!
There's no LOGo function.
Now, because this table here is sorted, the lookup function I'm going to use is LOOKUP.
That lookup_value, remember, it's giving us a bunch of values there, so that's an alright function array operation.
So it's going to look up all those things at once, and here's the array, that's this part right there.
So now if I close parenthesis, the way a function argument array operations work, since I gave that a bunch of values, LOOKUP will spit out a bunch of values.
There they are, so now I need to add those.
Great, that's an array, so I'm going to put it inside of SUMPRODUCT.
Now that'll give me the total, so that's the same total.
We could do that but we need to lock it.
Now I'm going to highlight that little colon, and F4, Ctrl+Enter, and double click and send it down.
Now I could use that custom number formatting, but how about this.
I'm going to use the CHOOSE function, which is a lookup function, and choose between "Under Zero", "Zero", and "Over Zero", but I need 1-2-3 as the index number for CHOOSE, so I'm going to- from that use the SIGN function!
What does the SIGN do?
The SIGN function simply gives a -1 if it's under 0, 0 if it's 0, and 1 if it's positive.
So now I want to ultimately use the CHOOSE function, I need 1-2-3, so I'm going to +2.
So now I have, for the CHOOSE, index number, that's exactly what it needs.
Now the cool thing about CHOOSE is, you simply can type your lookup values right in the formula.
So I think they want "Under Zero", if only I could type fast, "Zero", that'll be 2, and then "Over Zero", and there we go!
Bill: Alright, that's cool!
I am so glad that VBA is in Excel, because I don't have to try and figure out that formula.
Mike: Well, I'm so glad that the formulas do what they do, so I don't have to figure out VBA!
Bill: Alright, everyone's happy!
Alright.
Mike: But they do look similar, MID, LOOKUP...
Bill: You know it started out similar, but then when I used VLOOKUP, you used LOOKUP, and then you had to use CHOOSE and SIGN and SUMPROUCT, all those other things, so there you go.
Alright.
Mike: WOOHOO!
Bill: Hey, I'm sure we'll be back next week with another Dueling Excel podcast from MrExcel...
Mike: And ExcelIsFun!
Bill: Alright!
