Every once in a while, you run into ugly data sets and this qualifies. Someone decided it would be brilliant to put the text category and the sales amount in a single cell such as "WXYZ123". Today's duel is how to get the total sales for each category. Mike and Bill go back and forth 4 times today, offering a solution with Text to Columns, a Pivot Table, Array Formulas, and a VBA UDF.
Transcript of the video:
Welcome back it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel be joined by Mike Gervin from Excel Is Fun.
This is our episode 154 - Total 2871 TBS by the letters.
Hey Mike welcome back.
This is the shortest question we've ever had.
The question is how to get the totals. 4 words 18 characters.
I love that because they didn't give us a lot of detail which means we're allowed to do whatever we want. Here's the data over here and some brilliant person along the way decided that it would be great to put the numbers and the letters all in one cell.
So 2871. We want to find for all of the TBS entries what those first four digits are.
Here we go. Ready. Choose the data Alt+ D + E "Data" "Text to Columns" It is "Fixed Width" and we're going to draw that line and right after the fourth character. Click Finish.
There we go we now have the numbers and the Categories.
Good. Choose that data. "Insert" "Pivot Table" "Existing Worksheet" Right there in G1 Check mark "Cat" which goes to left. Check mark "Data". BAM!!
There's the answers.
Short question.. short answer.
Mike let's see what you have.
Thanks MrExcel.
That's it. That's the point "Text to Columns" "Pivot Table" Oh man fast and easy hey ah I'm going to try a Helper column here, so I'm going to go Helper and say LEFT of this , 4 That'll give me the number every time.
That's text F2 some math operation to convert a number stored as text back to a number.
Ctrl + Enter Double click and send it down.
Now I'm going to do just the straight SUMIF SUMIF. The range is gonna be the criteria range Comma whoops I forgot to lock it so I'm going to highlight the : F4 Alright so and then the criteria, one cell to my left relative cell reference, and then the SUM range F4 Close parentheses.
Ctrl whoa That didn't work at all.
Oh yeah TBS is not in this column, so I want to search for any text string that contains TBS and since the TBS here has stuff before, I'm going to join a wild card in double quotes. The wild card for 0 or more characters.
Double quote and join it.
Ctrl + Enter Duh have I just drag it and click it down whoops that one's not going to work.
And you know what I want to keep this empty.
Just because that's the way it came whoa wait a second.
If I look through here as criteria if the thing was greater than zero.
So really what I want is one of two different sets of criteria, so I'm going to say IF This cell right here, and I could go =" for empty, but also a formula that looks at an empty cell will see 0.
So I'm gonna put a 0 if it's 0 then I want 1 Set of criteria in ">0" that's the value if FALSE, that's dumping criteria into the criteria argument in SUMIF.
Otherwise I'm going to run that criteria right there.
Close parentheses.
Ctrl + Enter Double click and send it down and so now in this cell it's running.
Whereas up here the SUMIF is seeing that criteria.
All right throw it back to MrExcel.
Hey my cool trick with that wild card, but I really didn't like that you had to handle A5 the empty cell.
That was kind of kludgy, right?
So hey here's another way to go.
Let's make sure that we're allowed to run Macro.
So Alt + T + M + S for "Tools" "Macro" "Security" Make sure that you're not on that top one then we can add a Macro in here Alt + F11 Insert a module and you will type this. All right, so we're creating a brand new function called SUMTEXT We're going to pass at the range and we want to look at and the letters that we want to match, we have to initialize that variable to 0.
Look at each cell in the range, and I'm going to build two variables here MyLetters and MyNumbers.
I'm going to take that value in the cell and anything that's a digit 0 through 9.
I'm going to append to the end of MyNumbers and anything that's not a digit, I'm going to append it at the end of MyLetters.
So we'll loop through all the characters in that cell and then check to see if MyLetters is equal to what they passed.
If it is then we take the MyNumbers * 1 that converts it to a number and add it to the SumText and it'll just loop through all of the values now. I was really curious what's going to happen when we pass an empty cell?
What's going to happen to the letters, will that empty cell be = in essence "" so we'll come back here Alt + F 11 =sumtext and we are passing at this range.
Now this range or I'm going to lock down just like I would in a regular excel function and then compare it to TBS so there's our answer there. We'll copy that down.
Hold my breath for B5 and yes, it works the right answer back to you.
VBA now that's awesome because I can't do VBA But wait a second that Text to Column and Pivot Table was awesome.
Well okay, if you want to do something excessive remember "Texts and Columns" "Pivot Table" gets the point.
Well we could do a single cell array formula.
Notice we have a Helper column here.
We'll just simulate that Helper column right in our cell.
So taking all these numbers and throwing them into a single cell so our formula can look at it.
Hey instead of just a single cell in the text argument at left.
I'm going to give it the whole column F4 and say get the first four characters from the left.
If I highlight this whole a little bit F9 you can see Oh it's simulating that whole Helper column except for its text.
Ctrl + Z Do any math operation on it and it will convert it back to numbers.
F9 There we have our numbers or Helper column in a single cell.
Now I would like to use that inside of SUMIF for SUMIFs but guess what, some range criteria range arguments cannot handle array operations.
This is called a function argument array operation right there. SUMIFs can't handle it.
So what am I going to do instead of SUMIFs, I'm going to use SUMPRODUCT.
Now SUMPRODUCT has array array array and you have to multiply the arrays and then add the results. So we got all of our numbers.
I just need to multiply it by TRUEs or FALSEs or 1s and 0s So check this out.
I'm enough for the second array use the SEARCH function. The SEARCH can actually look in text strings and search for a sub text string.
So the text defined is going to be relative cell reference comma within, what? Well that's expecting a single cell.
We're going to give it an array just as with our left, and I better hit F4 here when I evaluate this because we gave it in this argument however many that is, it'll spit out that many answers.
F9 Notice what a SEARCH to it it tells you.
Hey I'm going to find that within the larger texturing and tell you the starting position so 5 value error for the rest of them.
Now, I can say are any of you numbers using ISNUMBER?
Now ISNUMBER is a logical function.
It'll spit out a bunch of TRUEs and FALSEs.
F9 SUMPRODUCT cannot understand TRUEs and FALSEs so we have to convert them to 1s and 0s.
Will do that with double negative.
I should have been clicking down here. Boop F9.
There's our 1s and 0s.
So one times the number in there, one times the number there, the zeros will zero out those numbers and SUMPRODUCT will be able to add Ctrl + Z Close parentheses.
SUMPRODUCT can handle array operations without Ctrl + Shift + Enter.
So I just Enter or Ctrl + Enter and copy it down oops..
That's the total with that criteria. It got all of the numbers so watch this.
I'm going to do some crazy. First off I'm going to use over to the side a little formula that say are you =0 Right and so that will give me TRUE and FALSE so boom there's our TRUE.
Convert it to a number and I'm going to put a minus just a single minus sign because I want 0 0 and minus 1.
Now I want to actually add the entire total. So watch this I'm going to or not add multiply, I want 0 times total zero times total zero times total and then minus 1 times total, so let's subtract, but not the total of the whole column of a running total.
So I'm going to come over here times SUM and the running total.
Now I'm eventually going to copy this and put it in the cell and subtract it and check this out the SUM function.
I'm going to click on the cell 1 above : close parenthesis and come back here and lock this because I need an expandable range. Now the cool thing about the SUM function is it will not be bothered with text.
If we were doing plus symbols. We get a value error, but no way SUM function won't work.
Just fine and check this out.
That's the running total there.
If I subtract that 25931 from this 34000 here.
I get exactly 8974. So I'm going to copy from the top in edit mode.
Ctrl + C Escape. Come up here F2 and then my cursor's at the end Ctrl + V Ctrl + Enter Double click and send it down and boom. There we go. That is ridiculous.
All right throw it back to MrExcel.
Well hey, I could keep going on with more VBA.
But it was the shortest question ever, can't be the longest video ever. So let's just call it there.
Well thank everyone for stopping by.
Will see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel be joined by Mike Gervin from Excel Is Fun.
This is our episode 154 - Total 2871 TBS by the letters.
Hey Mike welcome back.
This is the shortest question we've ever had.
The question is how to get the totals. 4 words 18 characters.
I love that because they didn't give us a lot of detail which means we're allowed to do whatever we want. Here's the data over here and some brilliant person along the way decided that it would be great to put the numbers and the letters all in one cell.
So 2871. We want to find for all of the TBS entries what those first four digits are.
Here we go. Ready. Choose the data Alt+ D + E "Data" "Text to Columns" It is "Fixed Width" and we're going to draw that line and right after the fourth character. Click Finish.
There we go we now have the numbers and the Categories.
Good. Choose that data. "Insert" "Pivot Table" "Existing Worksheet" Right there in G1 Check mark "Cat" which goes to left. Check mark "Data". BAM!!
There's the answers.
Short question.. short answer.
Mike let's see what you have.
Thanks MrExcel.
That's it. That's the point "Text to Columns" "Pivot Table" Oh man fast and easy hey ah I'm going to try a Helper column here, so I'm going to go Helper and say LEFT of this , 4 That'll give me the number every time.
That's text F2 some math operation to convert a number stored as text back to a number.
Ctrl + Enter Double click and send it down.
Now I'm going to do just the straight SUMIF SUMIF. The range is gonna be the criteria range Comma whoops I forgot to lock it so I'm going to highlight the : F4 Alright so and then the criteria, one cell to my left relative cell reference, and then the SUM range F4 Close parentheses.
Ctrl whoa That didn't work at all.
Oh yeah TBS is not in this column, so I want to search for any text string that contains TBS and since the TBS here has stuff before, I'm going to join a wild card in double quotes. The wild card for 0 or more characters.
Double quote and join it.
Ctrl + Enter Duh have I just drag it and click it down whoops that one's not going to work.
And you know what I want to keep this empty.
Just because that's the way it came whoa wait a second.
If I look through here as criteria if the thing was greater than zero.
So really what I want is one of two different sets of criteria, so I'm going to say IF This cell right here, and I could go =" for empty, but also a formula that looks at an empty cell will see 0.
So I'm gonna put a 0 if it's 0 then I want 1 Set of criteria in ">0" that's the value if FALSE, that's dumping criteria into the criteria argument in SUMIF.
Otherwise I'm going to run that criteria right there.
Close parentheses.
Ctrl + Enter Double click and send it down and so now in this cell it's running.
Whereas up here the SUMIF is seeing that criteria.
All right throw it back to MrExcel.
Hey my cool trick with that wild card, but I really didn't like that you had to handle A5 the empty cell.
That was kind of kludgy, right?
So hey here's another way to go.
Let's make sure that we're allowed to run Macro.
So Alt + T + M + S for "Tools" "Macro" "Security" Make sure that you're not on that top one then we can add a Macro in here Alt + F11 Insert a module and you will type this. All right, so we're creating a brand new function called SUMTEXT We're going to pass at the range and we want to look at and the letters that we want to match, we have to initialize that variable to 0.
Look at each cell in the range, and I'm going to build two variables here MyLetters and MyNumbers.
I'm going to take that value in the cell and anything that's a digit 0 through 9.
I'm going to append to the end of MyNumbers and anything that's not a digit, I'm going to append it at the end of MyLetters.
So we'll loop through all the characters in that cell and then check to see if MyLetters is equal to what they passed.
If it is then we take the MyNumbers * 1 that converts it to a number and add it to the SumText and it'll just loop through all of the values now. I was really curious what's going to happen when we pass an empty cell?
What's going to happen to the letters, will that empty cell be = in essence "" so we'll come back here Alt + F 11 =sumtext and we are passing at this range.
Now this range or I'm going to lock down just like I would in a regular excel function and then compare it to TBS so there's our answer there. We'll copy that down.
Hold my breath for B5 and yes, it works the right answer back to you.
VBA now that's awesome because I can't do VBA But wait a second that Text to Column and Pivot Table was awesome.
Well okay, if you want to do something excessive remember "Texts and Columns" "Pivot Table" gets the point.
Well we could do a single cell array formula.
Notice we have a Helper column here.
We'll just simulate that Helper column right in our cell.
So taking all these numbers and throwing them into a single cell so our formula can look at it.
Hey instead of just a single cell in the text argument at left.
I'm going to give it the whole column F4 and say get the first four characters from the left.
If I highlight this whole a little bit F9 you can see Oh it's simulating that whole Helper column except for its text.
Ctrl + Z Do any math operation on it and it will convert it back to numbers.
F9 There we have our numbers or Helper column in a single cell.
Now I would like to use that inside of SUMIF for SUMIFs but guess what, some range criteria range arguments cannot handle array operations.
This is called a function argument array operation right there. SUMIFs can't handle it.
So what am I going to do instead of SUMIFs, I'm going to use SUMPRODUCT.
Now SUMPRODUCT has array array array and you have to multiply the arrays and then add the results. So we got all of our numbers.
I just need to multiply it by TRUEs or FALSEs or 1s and 0s So check this out.
I'm enough for the second array use the SEARCH function. The SEARCH can actually look in text strings and search for a sub text string.
So the text defined is going to be relative cell reference comma within, what? Well that's expecting a single cell.
We're going to give it an array just as with our left, and I better hit F4 here when I evaluate this because we gave it in this argument however many that is, it'll spit out that many answers.
F9 Notice what a SEARCH to it it tells you.
Hey I'm going to find that within the larger texturing and tell you the starting position so 5 value error for the rest of them.
Now, I can say are any of you numbers using ISNUMBER?
Now ISNUMBER is a logical function.
It'll spit out a bunch of TRUEs and FALSEs.
F9 SUMPRODUCT cannot understand TRUEs and FALSEs so we have to convert them to 1s and 0s.
Will do that with double negative.
I should have been clicking down here. Boop F9.
There's our 1s and 0s.
So one times the number in there, one times the number there, the zeros will zero out those numbers and SUMPRODUCT will be able to add Ctrl + Z Close parentheses.
SUMPRODUCT can handle array operations without Ctrl + Shift + Enter.
So I just Enter or Ctrl + Enter and copy it down oops..
That's the total with that criteria. It got all of the numbers so watch this.
I'm going to do some crazy. First off I'm going to use over to the side a little formula that say are you =0 Right and so that will give me TRUE and FALSE so boom there's our TRUE.
Convert it to a number and I'm going to put a minus just a single minus sign because I want 0 0 and minus 1.
Now I want to actually add the entire total. So watch this I'm going to or not add multiply, I want 0 times total zero times total zero times total and then minus 1 times total, so let's subtract, but not the total of the whole column of a running total.
So I'm going to come over here times SUM and the running total.
Now I'm eventually going to copy this and put it in the cell and subtract it and check this out the SUM function.
I'm going to click on the cell 1 above : close parenthesis and come back here and lock this because I need an expandable range. Now the cool thing about the SUM function is it will not be bothered with text.
If we were doing plus symbols. We get a value error, but no way SUM function won't work.
Just fine and check this out.
That's the running total there.
If I subtract that 25931 from this 34000 here.
I get exactly 8974. So I'm going to copy from the top in edit mode.
Ctrl + C Escape. Come up here F2 and then my cursor's at the end Ctrl + V Ctrl + Enter Double click and send it down and boom. There we go. That is ridiculous.
All right throw it back to MrExcel.
Well hey, I could keep going on with more VBA.
But it was the shortest question ever, can't be the longest video ever. So let's just call it there.
Well thank everyone for stopping by.
Will see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
