Ram asks how to split a word into individual letters, and then count how many times each letter appears. Episode 1148 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, I have got a couple of cool formulas for today. Welcome back to the MrExcel netcast, I'm Bill Jelen.
Today’s question sent in via Facebook, from Ram. Ram has a big large word up here in cell A1, he needs to break that out letter by letter by letter, and count how many times the letters occur, so, very easy.
I’m going to say =MID of this big large word up here, press F4, and then we want to start at the right position, so we’re going to say ROW(1:1), that's just a geeky way of writing the number 1.
The nice thing is, it will change to 2 as we copy down for a length of 1.
So there's our first letter, as we can copy that down It gives us all the letters in a formula.
Check it out, the row 121 changes to 2:2, that gives us the 2nd character, and the 3rd character, and the 4th character, and the 5th character.
If you would need that to go across, then you would use COLUMN(A:A).
Alright, and then Ram says “I need to count how many times each thing occurs.” Well, that's just a simple little COUNTIF.
Look through all these letters over here, and see if it's equal to this particular letter, and we get our count.
So I sent that off to Ram, and I said “You know, I would bet that you probably only want to see the letter A up here once!” and he says “Yeah, it has to be unique!” Oh, oh, Oh!
This was so easy, so easy so easy so easy, and now it's so hard.
Shoot!
I shouldn't even asked, right, I should’ve just send them back and hope that he was happy, you know.
Now, one solution is just to come here, select that whole area, Insert PivotTable, OK!
Put the letters down the left-hand side, put the letters in the Sum of Values, and there's every letter and how many times it occurs, of course, that's not a formula solution.
So you know, the only thing I can come up with here, and I bounce this one off of Mike to see if maybe would be a good Dueling podcast, and Mike says “Oh yeah, this is an ugly one!” The only thing I could come up with this is this big huge array formula.
The array formula tries to do a FIND, It says “Let's go look for the first letter in the text, and when we get that, see if the FIND is equal to the particular row.” So in other words, look for that first A and see if it's in Row 1, if it is, then I want the A, otherwise I want “”. So what that's going to do is, when we find the first A it'll say “Well, this is at 1, oh good, I'm in row 1, give me that.” But when I'm down here later on, we get wherever the next A is, I don't know, let's see, it's down here in about the 11th character.
It'll say “Hey, well I'm currently looking at the 11th character.
Where is the FIND?” And the FIND is in the first position, and so that's not a match and we're going to get blanks.
Alright, so I press Ctrl+Shift+Enter, copy that down, and what you get then is, you get unique letters, but then you just get blank spaces where nothing is found.
And now unfortunately we can’t do a COUNTIF, because we don't have all the letters.
So we have a SUMPRODUCT out here, kind of an unusual use of SUMPRODUCT.
What I did was, I said “Go through, look for the MID of A1, give me all the values from 1-29, and see if that's equal to the current value A.” Had to put a minus before that.
So we're really going to just taking arrays of TRUEs and FALSEs, converting them to ones and zeros, and then summing them up to come up with that answer.
So really, overall, a very, very ugly question from Ram, you wouldn't expect that something that tough would come in from Facebook, right, Facebook is supposed to be fun!
Not in this case.
So hey, if you have a better solution, you know maybe a macro or VBA or something, feel free to shoot me a note, bill@mrexcel.com.
Make sure to stop by tomorrow for a Dueling podcast where Mike and I take a look at a very interesting problem.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, I have got a couple of cool formulas for today. Welcome back to the MrExcel netcast, I'm Bill Jelen.
Today’s question sent in via Facebook, from Ram. Ram has a big large word up here in cell A1, he needs to break that out letter by letter by letter, and count how many times the letters occur, so, very easy.
I’m going to say =MID of this big large word up here, press F4, and then we want to start at the right position, so we’re going to say ROW(1:1), that's just a geeky way of writing the number 1.
The nice thing is, it will change to 2 as we copy down for a length of 1.
So there's our first letter, as we can copy that down It gives us all the letters in a formula.
Check it out, the row 121 changes to 2:2, that gives us the 2nd character, and the 3rd character, and the 4th character, and the 5th character.
If you would need that to go across, then you would use COLUMN(A:A).
Alright, and then Ram says “I need to count how many times each thing occurs.” Well, that's just a simple little COUNTIF.
Look through all these letters over here, and see if it's equal to this particular letter, and we get our count.
So I sent that off to Ram, and I said “You know, I would bet that you probably only want to see the letter A up here once!” and he says “Yeah, it has to be unique!” Oh, oh, Oh!
This was so easy, so easy so easy so easy, and now it's so hard.
Shoot!
I shouldn't even asked, right, I should’ve just send them back and hope that he was happy, you know.
Now, one solution is just to come here, select that whole area, Insert PivotTable, OK!
Put the letters down the left-hand side, put the letters in the Sum of Values, and there's every letter and how many times it occurs, of course, that's not a formula solution.
So you know, the only thing I can come up with here, and I bounce this one off of Mike to see if maybe would be a good Dueling podcast, and Mike says “Oh yeah, this is an ugly one!” The only thing I could come up with this is this big huge array formula.
The array formula tries to do a FIND, It says “Let's go look for the first letter in the text, and when we get that, see if the FIND is equal to the particular row.” So in other words, look for that first A and see if it's in Row 1, if it is, then I want the A, otherwise I want “”. So what that's going to do is, when we find the first A it'll say “Well, this is at 1, oh good, I'm in row 1, give me that.” But when I'm down here later on, we get wherever the next A is, I don't know, let's see, it's down here in about the 11th character.
It'll say “Hey, well I'm currently looking at the 11th character.
Where is the FIND?” And the FIND is in the first position, and so that's not a match and we're going to get blanks.
Alright, so I press Ctrl+Shift+Enter, copy that down, and what you get then is, you get unique letters, but then you just get blank spaces where nothing is found.
And now unfortunately we can’t do a COUNTIF, because we don't have all the letters.
So we have a SUMPRODUCT out here, kind of an unusual use of SUMPRODUCT.
What I did was, I said “Go through, look for the MID of A1, give me all the values from 1-29, and see if that's equal to the current value A.” Had to put a minus before that.
So we're really going to just taking arrays of TRUEs and FALSEs, converting them to ones and zeros, and then summing them up to come up with that answer.
So really, overall, a very, very ugly question from Ram, you wouldn't expect that something that tough would come in from Facebook, right, Facebook is supposed to be fun!
Not in this case.
So hey, if you have a better solution, you know maybe a macro or VBA or something, feel free to shoot me a note, bill@mrexcel.com.
Make sure to stop by tomorrow for a Dueling podcast where Mike and I take a look at a very interesting problem.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
