Today, Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen are faced with taking the Data from a 'Data Dump' file and Counting - by category - to find the totals. Follow along with Mike and Bill for Formulas, Shortcuts and more!
Dueling Excel Podcast #103...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! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
Dueling Excel Podcast #103...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! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
Transcript of the video:
Mike Girvin: Welcome to Dueling Podcast #105.
I'm Mike Girvin from Excel Is Fun and Bill ‘MrExcel’ Jelen will be along in a few moments.
Hey, this week we have a data dump we get and it's a report, data dumped by every month.
And we have these groups: Red, and there's two people there; Amber, and there's three people here; and Green, and there's five people there.
And we need to count between.
So over on a different sheet, I need to count how many people are in the Red group, how many people in the Amber.
Now I could create a formula with sheet reference, and work back and forth but I'm going to cheat here.
This is a great trick I'm going to copy this little template, and paste it over here.
Build my formulas here and then when I cut and paste at the end, it will create the sheet references on this sheet.
Alright now, so count between.
So for Red, we need to count between Red and Amber, for Amber we need to count between Amber and Green; but, when we get down here to Green, we need to count just everything below Green.
Now, a couple of things here.
Sometimes we might have nobody in a particular group or we might have lots of people.
In addition, we have just three groups here.
You could have many more then you just list them here.
Now I'm going to treat this as a Lookup, and I'm going to Lookup Red and Amber and help- and use that information to help create a dynamic range to then count non-empty cells.
I’m going to use the INDEX function and the MATCH function to an essence Lookup a position.
Alright, so the Array, I'm going to have - I have no idea how big each month’s list will be, so I'm going to select the whole column and I'm going to use the MATCH function, I'm going to Lookup Red.
Red within Column A, comma and 0 for exact match because the information is not sorted.
Now, right now, if I highlight this and hit the F9, it gives me 18 which is perfect.
It's the 18th row, Ctrl+Z, if I highlight the whole index.
Now, I want to Lookup a cell reference and create a dynamic range but watch this: if I hit the F9 key, index is programmed to retrieve a value so it's retrieving Red.
Ctrl+Z, I'm going to copy this and I'm going to put the INDEX function in the context of a range or cell range.
Notice a :A or if we had over here D3:D20, the colon is the context which says I am a range of cells.
So I'm going to type a colon, Ctrl+V to paste that INDEX, and from the second INDEX I don't want to Lookup Red.
I want to Lookup Amber.
Now, this index right here like the first one, if I hit F9 is returning Amber, Ctrl+Z.
But, if I highlight the whole thing including that colon and hit F9 – Boom!
It's returning a range.
And when I copy this formula down, it will move from Red to Amber, to Amber, to Green.
So Ctrl+Z.
Now, I want to count non-empty cells, so I’m going to choose COUNTA counts non-empty cells.
Close parenthesis, Ctrl+Enter and copy down.
Now, that’s not quite what we want.
And also we have a problem here but, we always are counting in what?
Red, Mickey Mouse, Amber so I’m always going to subtract 2 to get rid of the bookends in essence, right?
There’s Red and Amber bookends here when we copy down, it will be Amber and Green.
Alright, so that's looking good for 2 and 3.
If we were to right-click here and Delete, you could see it's picking up a 0 because there's nothing in the Red group, Ctrl+Z.
Now here, and I don't have a real good method to fix this.
The problem is up between Red and Amber; we have two bookends, right?
Amber and Green, two bookends but here we don't have a second bookend.
Now watch this, here's a great cheat.
If I could come down to the end, each month I get this report and just type Last, and then have in my template toward Last.
It would get it right, right?
Ahh, but perhaps I don't.
So, this is going to get messy here.
Well, what's causing the problem?
That blank there.
So this is delivering in a position which doesn't exist, so I'm going to change this.
I'm going to say IF that relative position there =”” which is just saying that cell empty, then what do I want?
Well, what does MATCH delivering?
It’s delivering a row number, so I'm just going to put some big row number; the most people that'll ever be there is a thousand so I'm going to put 5,000.
So that's the value of true meaning that cell is blank; otherwise, just run that.
Now this still quite- won't quite work because what is happening up here, we're -2 because we have Red and Amber, 2 bookmarks.
Down here it's -2 and we don't have 2 bookmarks.
Again, I don't have a real good solution for this.
So, at the end I need to subtract some number either 1 or 2, so I’m going to say IF this =”” that means it's blank, then I want to -1, right?
Because Green doesn't have another value down here so I just -1 comma, otherwise 2.
Ooh, I'm not so sure.
I think that's the best way.
Hopefully someone knows a better way to do this.
I'm sure MrExcel will show us a better way.
But there it is!
And now I want to cut and paste this over here, just be careful.
That cell reference is looking right there so be sure and cut that: Ctrl+X, Ctrl+V, and there we have it.
Now, next month if we get a report like this: Copy and I paste it right here, including all those stuff at the top.
I should get 0, 0 and maybe 7.
So, 0, 0, 7, alright.
Throw it over to MrExcel.
I hope you have a better way.
Bill Jelen: Hey, alright, Mike, that's a cool formula.
I love your cut and paste to keep the- to create the worksheet.
Name references, that was brilliant.
I, of course, switch right over to VBA on this and set up some counters for Red, Amber, Green and figure out - We're going to go from the first row down to whatever the last row is.
My famous line of code there to figure out the last row.
And I'm looking for things up in the header.
If I see that the date – how do I save the date if I’m looking- if I find a Red, Amber, Green I set that color.
And then there was a whole bunch of stuff that I had to ignore, alright.
So if we hadn't found the first color don't count anything.
There was a note in one of the sheets, a few other things in the sheets we had to ignore.
But then, here's where the good stuff happens.
Once we know that we have a non-blank cell that I'm going to presume as a patient and we're down in the color section, then I look at the current color and add 1 to either Red Count, Amber Count, Green Count; or, I created one called Problem Count, just in case there was ever a day where I got something that I didn't expect and then report this back – What she called?
Report.
We’re going to report the date, the Red, the Amber, the Green and the problem.
Alright, and so, this is- has a loop in it so I’ll go through all of the sheets in the workbook.
We just have to press Ctrl+Shift+C. BAM!
And so, there.
For each of the worksheets I get the date and the number of colors.
Let’s just check.
So here on the second sheet, we should have no Red, no Amber and 7 Green.
No Red, no Amber, and 7 Green.
So that works.
I even think that this will handle the case where there is the Amber is missing altogether.
Let’s – I try that.
So Ctrl+Shift+C, ahh yeah, it doesn’t get fooled by that.
Now, if someone adds a new color in, Purple, that of course the Macro is going to have to be rewritten.
Oh hey, I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel Podcast from MrExcel and Excel Is Fun.
I'm Mike Girvin from Excel Is Fun and Bill ‘MrExcel’ Jelen will be along in a few moments.
Hey, this week we have a data dump we get and it's a report, data dumped by every month.
And we have these groups: Red, and there's two people there; Amber, and there's three people here; and Green, and there's five people there.
And we need to count between.
So over on a different sheet, I need to count how many people are in the Red group, how many people in the Amber.
Now I could create a formula with sheet reference, and work back and forth but I'm going to cheat here.
This is a great trick I'm going to copy this little template, and paste it over here.
Build my formulas here and then when I cut and paste at the end, it will create the sheet references on this sheet.
Alright now, so count between.
So for Red, we need to count between Red and Amber, for Amber we need to count between Amber and Green; but, when we get down here to Green, we need to count just everything below Green.
Now, a couple of things here.
Sometimes we might have nobody in a particular group or we might have lots of people.
In addition, we have just three groups here.
You could have many more then you just list them here.
Now I'm going to treat this as a Lookup, and I'm going to Lookup Red and Amber and help- and use that information to help create a dynamic range to then count non-empty cells.
I’m going to use the INDEX function and the MATCH function to an essence Lookup a position.
Alright, so the Array, I'm going to have - I have no idea how big each month’s list will be, so I'm going to select the whole column and I'm going to use the MATCH function, I'm going to Lookup Red.
Red within Column A, comma and 0 for exact match because the information is not sorted.
Now, right now, if I highlight this and hit the F9, it gives me 18 which is perfect.
It's the 18th row, Ctrl+Z, if I highlight the whole index.
Now, I want to Lookup a cell reference and create a dynamic range but watch this: if I hit the F9 key, index is programmed to retrieve a value so it's retrieving Red.
Ctrl+Z, I'm going to copy this and I'm going to put the INDEX function in the context of a range or cell range.
Notice a :A or if we had over here D3:D20, the colon is the context which says I am a range of cells.
So I'm going to type a colon, Ctrl+V to paste that INDEX, and from the second INDEX I don't want to Lookup Red.
I want to Lookup Amber.
Now, this index right here like the first one, if I hit F9 is returning Amber, Ctrl+Z.
But, if I highlight the whole thing including that colon and hit F9 – Boom!
It's returning a range.
And when I copy this formula down, it will move from Red to Amber, to Amber, to Green.
So Ctrl+Z.
Now, I want to count non-empty cells, so I’m going to choose COUNTA counts non-empty cells.
Close parenthesis, Ctrl+Enter and copy down.
Now, that’s not quite what we want.
And also we have a problem here but, we always are counting in what?
Red, Mickey Mouse, Amber so I’m always going to subtract 2 to get rid of the bookends in essence, right?
There’s Red and Amber bookends here when we copy down, it will be Amber and Green.
Alright, so that's looking good for 2 and 3.
If we were to right-click here and Delete, you could see it's picking up a 0 because there's nothing in the Red group, Ctrl+Z.
Now here, and I don't have a real good method to fix this.
The problem is up between Red and Amber; we have two bookends, right?
Amber and Green, two bookends but here we don't have a second bookend.
Now watch this, here's a great cheat.
If I could come down to the end, each month I get this report and just type Last, and then have in my template toward Last.
It would get it right, right?
Ahh, but perhaps I don't.
So, this is going to get messy here.
Well, what's causing the problem?
That blank there.
So this is delivering in a position which doesn't exist, so I'm going to change this.
I'm going to say IF that relative position there =”” which is just saying that cell empty, then what do I want?
Well, what does MATCH delivering?
It’s delivering a row number, so I'm just going to put some big row number; the most people that'll ever be there is a thousand so I'm going to put 5,000.
So that's the value of true meaning that cell is blank; otherwise, just run that.
Now this still quite- won't quite work because what is happening up here, we're -2 because we have Red and Amber, 2 bookmarks.
Down here it's -2 and we don't have 2 bookmarks.
Again, I don't have a real good solution for this.
So, at the end I need to subtract some number either 1 or 2, so I’m going to say IF this =”” that means it's blank, then I want to -1, right?
Because Green doesn't have another value down here so I just -1 comma, otherwise 2.
Ooh, I'm not so sure.
I think that's the best way.
Hopefully someone knows a better way to do this.
I'm sure MrExcel will show us a better way.
But there it is!
And now I want to cut and paste this over here, just be careful.
That cell reference is looking right there so be sure and cut that: Ctrl+X, Ctrl+V, and there we have it.
Now, next month if we get a report like this: Copy and I paste it right here, including all those stuff at the top.
I should get 0, 0 and maybe 7.
So, 0, 0, 7, alright.
Throw it over to MrExcel.
I hope you have a better way.
Bill Jelen: Hey, alright, Mike, that's a cool formula.
I love your cut and paste to keep the- to create the worksheet.
Name references, that was brilliant.
I, of course, switch right over to VBA on this and set up some counters for Red, Amber, Green and figure out - We're going to go from the first row down to whatever the last row is.
My famous line of code there to figure out the last row.
And I'm looking for things up in the header.
If I see that the date – how do I save the date if I’m looking- if I find a Red, Amber, Green I set that color.
And then there was a whole bunch of stuff that I had to ignore, alright.
So if we hadn't found the first color don't count anything.
There was a note in one of the sheets, a few other things in the sheets we had to ignore.
But then, here's where the good stuff happens.
Once we know that we have a non-blank cell that I'm going to presume as a patient and we're down in the color section, then I look at the current color and add 1 to either Red Count, Amber Count, Green Count; or, I created one called Problem Count, just in case there was ever a day where I got something that I didn't expect and then report this back – What she called?
Report.
We’re going to report the date, the Red, the Amber, the Green and the problem.
Alright, and so, this is- has a loop in it so I’ll go through all of the sheets in the workbook.
We just have to press Ctrl+Shift+C. BAM!
And so, there.
For each of the worksheets I get the date and the number of colors.
Let’s just check.
So here on the second sheet, we should have no Red, no Amber and 7 Green.
No Red, no Amber, and 7 Green.
So that works.
I even think that this will handle the case where there is the Amber is missing altogether.
Let’s – I try that.
So Ctrl+Shift+C, ahh yeah, it doesn’t get fooled by that.
Now, if someone adds a new color in, Purple, that of course the Macro is going to have to be rewritten.
Oh hey, I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel Podcast from MrExcel and Excel Is Fun.
