A Question for Dueling Excel: "How can I count up these Egg Customer Purchase Quantities?" We have the count per tray, we have the dates, the names and the values - now we need to get all of these eggs into one basket for inventory. Following along as Mike :ExcelIsFun" Girvin and Bill "MrExcel" Jelen take their customary approaches to solving this week's Dueling Excel Question!
Dueling Excel Podcast #122...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] Slaying Excel Dragons
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!
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 Podcast #122...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] Slaying Excel Dragons
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!
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 time for another Dueling Excel podcast, episode 127 - Alternate Number Systems!
Alright, today's question sent in, trying to add up eggs!
Alright, so 10-15 means 10 trays + 15 eggs, each tray is 30 eggs.
And, we want to be able to add these up, and then reduce it, so we wouldn't want to say something like, you know, 12 trays - 65 eggs, we want to take that 65 and reduce it, say that's 2 trays + 5 extra eggs.
So, the only way that I know to attack this is, going to switch over to VBA and write a custom function.
So let's do Alt+F11 here, and I wrote a function called SumEggs, so.
You know, we have a SUM function in Excel, this going to be the SumEggs function.
We're passing "MyRange", just a couple of shortcuts here to define WF as Application.WorksheetFunction, that way we don't have to type it here.
We're going to loop through each cell in the range, so if we pass it 4 cells or 50 cells, it's going to go through each one, one at a time, and it's going to do a Find to figure out where the dash occurs.
So WF.Find , WorksheetFunction.Find , find the dash in Cel.Value . Now this quick and dirty function here is going to assume that there's a dash everywhere.
We take the Left of Cell.Value starting at WhereDash - 1, that's the number of cases, multiply that by 30, take the MID of Cell.Value at WhereDash + 1.
In other words, right after the dash.
Get all those characters, and that's the number of loose eggs, in addition to the full tray.
So this is a single line of code, these are continuation characters here, so this single line of code is adding up the total number of eggs.
We're kind of throwing out the trays at this point, we're just adding eggs, adding eggs, adding eggs, adding eggs, and then go back and get the next cell.
After we processed all the cells, we have an egg count, so we can calculate the FullCase, that should actually be FullTray I guess.
Take the integer of the account divided by 30, and then how many eggs are left over, that's EggCount - (30 * FullCase).
And then we're going to return a value back to Excel, and we have to assign the text to a variable called SumEggs, because that's the name of the function.
So FullCase & "-" & LeftOver.
Alright so, let's give it a try.
So =SumEggs, specify this range here, 131-5, will copy that all the way across, and then here =SumEggs, oops.
It should be able to work going across, no hassles there, 21-25, copy that down.
Yes, and these, check out the numbers that were manually calculated beforehand.
Alright, so a quick little, this is called a user-defined function in VBA, to allow us to do those calculations.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel!
Wow, this question, this is a tough one, this is definitely one of the harder ones that we've ever tried to answer.
And actually, looking at this right here, since I already know there is no formula that can do this, I'm just going steal your SumEggs, come over to the sheet and do the SumEgg- Well OK, I guess there is a formula, but this is going to be crazy!
Now, I'm going to go ahead and do it in multiple steps here, just so we can see the different pieces of the formula, then we'll put it all together, and we'll see a few other good tricks too.
Now the essence of this is there's a dash, right?
Everything to the left are the trays, we need to add those up.
Everything to the right are the eggs, we're going to need to do two things with those: get the /30, get the integer part for trays, and then whatever eggs are left over.
Oh, alright.
So I'm going to come here, the first thing is we could see this dash, and it's everything to the left, so I'm going to try the LEFT function.
And I'm just going to do a function argument array operation.
Now this text, I'm going to give it all of these.
Notice we have 6 cells into that argument, that's expecting a single item, so that means the LEFT function is going to spit out 6 answers.
Now comma, number of characters, we just need to know where the - is, right, it's 3 there, it's 2 there.
I'm going to use SEARCH.
Now SEARCH and FIND are the same, except for SEARCH is not case-sensitive.
I'm going to search for that "-", WITHIN, well, we need to give it a second function here, we're going to give this argument here more than one value.
So it's another function argument array operation.
Again, now SEARCH will spit out 6 answers, because we gave it 6 cells there.
F9, we can see it gives us an array, we need to -1 from each one of those to get the actual position that the LEFT needs to use.
We can close that off, if I highlight this, we can see that the left is now, F9, spitting out exactly what we need.
Now those are text numbers, Ctrl+Z, so we add, or any math operation will convert those text numbers back to number.
So I’m going to F9 and there we go.
I used +0, you could use double-negative if you want.
Now to get Total trays, I could use the SUM, but this would require Ctrl+Shift+Enter because it's got an array operation.
But I'm going to use SUMPRODUCT, it doesn't require Ctrl+Shift+Enter, that argument there can handle array operations.
We're not using SUMPRODUCT to multiply, we're just using it to add this array.
Ctrl+Enter, copy it over, that's the total number of trays from the LEFT.
Now we need to get everything from the right, and I could use RIGHT and then SEARCH and LEN, but I'm going to use the REPLACE function.
Now, the REPLACE needs the old text, another function argument array operation.
REPLACE says "Hey, where do you want to start?" I'm going to start at character #1, and go all the way to that position of the dash.
And again, we're going to have to make another function argument array operation there, this will be an array of as(?), but REPLACE is going to go from 1 all the way to 3 here, and it's going to ask me what's the new text, I'm going to use "" which is a null text, but here it will instruct REPLACE to put nothing.
Alright, so if I highlight this, and F9, you can see there are our numbers from the right, those are the number of eggs.
Ctrl+Z, I'm going to add 0, and that will give me all the numbers, and I can use SUMPRODUCT to add.
So this is the total eggs, now we have total eggs, and we need to do two things, one is get 30/65=2, and then the remainder.
Guess what, I'm going to copy this little piece right here, Ctrl+C, and I'm going to use the QUOTIENT function.
Now why the QUOTIENT?
Because QUOTIENT is related to MOD, QUOTIENT can take something in the numerator, and if I comma divided by 30, it gives me the integer part.
So there's the number of trays on the right, and now I come over here and do the related MOD, that same little bit divided by 30, and this gives me whatever the remainder is, so there we go.
Now, this is the little bit that needs to be joined with the dash to the end, and we need to add the 65 in this too.
I'm going to use the clipboard, I'm going it Clear All.
I'm going to copy this which is the Total trays from the left, Ctrl+C, this is the total trays from the right, Ctrl+C, I'm loading them up over here.
And then this is the last little bit, we need to join together with a dash!
So I come down here, equals, I need to take all the trays from the left, and add it to all the trays on the right, and then join it.
Shift+7 to get to the &"-" and join it to the MOD!
You've got to be kidding me, that is a gigantic formula.
Ctrl+Enter, that is just ridiculous.
And there we have it.
Now, if we need it up here also, check this out.
I'm going to copy this in edit mode, Ctrl+C, and come up here on Ctrl+V,.
And guess what, I need to take all the C2:C7 and replace it with C2:E2.
If I did REPLACE right now with a single cell highlighted, it would do this cell, and this cell.
In essence it will look through all of the cells in the spreadsheet, so I'm going to trick it by highlighting one cell next to it.
Ctrl+H, whoops, I did Alt+H. Ctrl+H, I'm going to look for all the C2:C7 and replace C2 to E2 to Replace All.
Click OK, click Close, and copy this down.
Wow, that is insane, it is amazingly insane what we humans require Excel to do.
I'd rather just use that SumEggs function than that big array formula.
And I'm sure there's actually an easier way to do it, but that's the best I could think of.
Alright, we'll throw back to MrExcel!
Bill: Wow Mike, that is a thing of beauty, that formula makes me say "Who needs VBA!" And this trick over here, copying all the bits over to the clipboard, I love that.
And then the replace up here, brilliant.
Point to excelisfun-
Alright, today's question sent in, trying to add up eggs!
Alright, so 10-15 means 10 trays + 15 eggs, each tray is 30 eggs.
And, we want to be able to add these up, and then reduce it, so we wouldn't want to say something like, you know, 12 trays - 65 eggs, we want to take that 65 and reduce it, say that's 2 trays + 5 extra eggs.
So, the only way that I know to attack this is, going to switch over to VBA and write a custom function.
So let's do Alt+F11 here, and I wrote a function called SumEggs, so.
You know, we have a SUM function in Excel, this going to be the SumEggs function.
We're passing "MyRange", just a couple of shortcuts here to define WF as Application.WorksheetFunction, that way we don't have to type it here.
We're going to loop through each cell in the range, so if we pass it 4 cells or 50 cells, it's going to go through each one, one at a time, and it's going to do a Find to figure out where the dash occurs.
So WF.Find , WorksheetFunction.Find , find the dash in Cel.Value . Now this quick and dirty function here is going to assume that there's a dash everywhere.
We take the Left of Cell.Value starting at WhereDash - 1, that's the number of cases, multiply that by 30, take the MID of Cell.Value at WhereDash + 1.
In other words, right after the dash.
Get all those characters, and that's the number of loose eggs, in addition to the full tray.
So this is a single line of code, these are continuation characters here, so this single line of code is adding up the total number of eggs.
We're kind of throwing out the trays at this point, we're just adding eggs, adding eggs, adding eggs, adding eggs, and then go back and get the next cell.
After we processed all the cells, we have an egg count, so we can calculate the FullCase, that should actually be FullTray I guess.
Take the integer of the account divided by 30, and then how many eggs are left over, that's EggCount - (30 * FullCase).
And then we're going to return a value back to Excel, and we have to assign the text to a variable called SumEggs, because that's the name of the function.
So FullCase & "-" & LeftOver.
Alright so, let's give it a try.
So =SumEggs, specify this range here, 131-5, will copy that all the way across, and then here =SumEggs, oops.
It should be able to work going across, no hassles there, 21-25, copy that down.
Yes, and these, check out the numbers that were manually calculated beforehand.
Alright, so a quick little, this is called a user-defined function in VBA, to allow us to do those calculations.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel!
Wow, this question, this is a tough one, this is definitely one of the harder ones that we've ever tried to answer.
And actually, looking at this right here, since I already know there is no formula that can do this, I'm just going steal your SumEggs, come over to the sheet and do the SumEgg- Well OK, I guess there is a formula, but this is going to be crazy!
Now, I'm going to go ahead and do it in multiple steps here, just so we can see the different pieces of the formula, then we'll put it all together, and we'll see a few other good tricks too.
Now the essence of this is there's a dash, right?
Everything to the left are the trays, we need to add those up.
Everything to the right are the eggs, we're going to need to do two things with those: get the /30, get the integer part for trays, and then whatever eggs are left over.
Oh, alright.
So I'm going to come here, the first thing is we could see this dash, and it's everything to the left, so I'm going to try the LEFT function.
And I'm just going to do a function argument array operation.
Now this text, I'm going to give it all of these.
Notice we have 6 cells into that argument, that's expecting a single item, so that means the LEFT function is going to spit out 6 answers.
Now comma, number of characters, we just need to know where the - is, right, it's 3 there, it's 2 there.
I'm going to use SEARCH.
Now SEARCH and FIND are the same, except for SEARCH is not case-sensitive.
I'm going to search for that "-", WITHIN, well, we need to give it a second function here, we're going to give this argument here more than one value.
So it's another function argument array operation.
Again, now SEARCH will spit out 6 answers, because we gave it 6 cells there.
F9, we can see it gives us an array, we need to -1 from each one of those to get the actual position that the LEFT needs to use.
We can close that off, if I highlight this, we can see that the left is now, F9, spitting out exactly what we need.
Now those are text numbers, Ctrl+Z, so we add, or any math operation will convert those text numbers back to number.
So I’m going to F9 and there we go.
I used +0, you could use double-negative if you want.
Now to get Total trays, I could use the SUM, but this would require Ctrl+Shift+Enter because it's got an array operation.
But I'm going to use SUMPRODUCT, it doesn't require Ctrl+Shift+Enter, that argument there can handle array operations.
We're not using SUMPRODUCT to multiply, we're just using it to add this array.
Ctrl+Enter, copy it over, that's the total number of trays from the LEFT.
Now we need to get everything from the right, and I could use RIGHT and then SEARCH and LEN, but I'm going to use the REPLACE function.
Now, the REPLACE needs the old text, another function argument array operation.
REPLACE says "Hey, where do you want to start?" I'm going to start at character #1, and go all the way to that position of the dash.
And again, we're going to have to make another function argument array operation there, this will be an array of as(?), but REPLACE is going to go from 1 all the way to 3 here, and it's going to ask me what's the new text, I'm going to use "" which is a null text, but here it will instruct REPLACE to put nothing.
Alright, so if I highlight this, and F9, you can see there are our numbers from the right, those are the number of eggs.
Ctrl+Z, I'm going to add 0, and that will give me all the numbers, and I can use SUMPRODUCT to add.
So this is the total eggs, now we have total eggs, and we need to do two things, one is get 30/65=2, and then the remainder.
Guess what, I'm going to copy this little piece right here, Ctrl+C, and I'm going to use the QUOTIENT function.
Now why the QUOTIENT?
Because QUOTIENT is related to MOD, QUOTIENT can take something in the numerator, and if I comma divided by 30, it gives me the integer part.
So there's the number of trays on the right, and now I come over here and do the related MOD, that same little bit divided by 30, and this gives me whatever the remainder is, so there we go.
Now, this is the little bit that needs to be joined with the dash to the end, and we need to add the 65 in this too.
I'm going to use the clipboard, I'm going it Clear All.
I'm going to copy this which is the Total trays from the left, Ctrl+C, this is the total trays from the right, Ctrl+C, I'm loading them up over here.
And then this is the last little bit, we need to join together with a dash!
So I come down here, equals, I need to take all the trays from the left, and add it to all the trays on the right, and then join it.
Shift+7 to get to the &"-" and join it to the MOD!
You've got to be kidding me, that is a gigantic formula.
Ctrl+Enter, that is just ridiculous.
And there we have it.
Now, if we need it up here also, check this out.
I'm going to copy this in edit mode, Ctrl+C, and come up here on Ctrl+V,.
And guess what, I need to take all the C2:C7 and replace it with C2:E2.
If I did REPLACE right now with a single cell highlighted, it would do this cell, and this cell.
In essence it will look through all of the cells in the spreadsheet, so I'm going to trick it by highlighting one cell next to it.
Ctrl+H, whoops, I did Alt+H. Ctrl+H, I'm going to look for all the C2:C7 and replace C2 to E2 to Replace All.
Click OK, click Close, and copy this down.
Wow, that is insane, it is amazingly insane what we humans require Excel to do.
I'd rather just use that SumEggs function than that big array formula.
And I'm sure there's actually an easier way to do it, but that's the best I could think of.
Alright, we'll throw back to MrExcel!
Bill: Wow Mike, that is a thing of beauty, that formula makes me say "Who needs VBA!" And this trick over here, copying all the bits over to the clipboard, I love that.
And then the replace up here, brilliant.
Point to excelisfun-
