Betsie has a Planting Schedule. The Workbook contains the Crop, Variety, Desired Plant Date and more. Now with last years Data, Betsie wants to Plan this years Planting Schedule. Follow along with Episode #1682 as Mike and Bill as they figure out the methods to create an even easier way to arrive at the proper Planting dates for this season.
Dueling Excel Podcast #118...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 #118...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: Welcome back, it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
We will be joined by Mike Girvin from Excel Is Fun.
This is episode 119: Sort by just month and day.
I love this problem.
This is a real-life problem.
Betsy from Olympia, Washington is keeping track of planting dates.
She started the spreadsheet last year and here's how it works.
She puts in the crop types, a variety and hey we planted this on 4/22 and 5 Row feet.
Right, everything's good.
Last year she started this spreadsheet and started again this year and now we actually want to use this spreadsheet all right, to do some planning.
We want to be able to sort this data by desired plant date.
Betsy I got rid of your row one up here because it was --we needed a break between the headings and the title I could have inserted a row I just got rid of the title and we sort ascending and it doesn't work.
Right, here's March, here's May, here's June and here's July.
Back to March, what's going on?
Well Betsy had discovered that in Excel when she enters 4/15, Excel is secretly adding a year.
So the things she entered last year have 2012 and the things she entered this year have 2013.
How do we stop that?
We don't want Excel to add that year or is there some way to sort by month and day?
All right, now Betsy’s solution, which I think is brilliant, I never realized this would work.
We just Ctrl+ H for Find and Replace.
Change area occurrence of 2012 to 2013 and click Replace All and sure enough that actually works.
I can't believe that it works.
All right, so our solution has to be easier than that solution.
I don't know it's going to be but you know I had a couple of ideas.
I had thought maybe a macro would work.
She changed macro that grabs the date 3/15 and puts it back to 1900 all the time or a macro here select these dates hit the icon in the quick access toolbar and it converts everything to the current year, that would be an easy little macro but I'm going to give credit to Sam Rad from the Excel team.
Sam came up with this idea we go back to formatting.
I'm adding this in the way that Betsy wants it formatted, just month and day.
I'm going take that data Ctrl+C, new workbook Ctrl+N and Ctrl+V.
So there's our original and I'm going format this as text.
Does Betsy have to do every single time she wants to sort?
No, this is a one-time thing.
Actually I'm going make it a two -time thing because I need to prove that it works and then we'll do it for real.
So there's the text that we want to display and then I'm going do a Sort By in C2 and this is going be =text(A2,”mmdd”) and we have something to sort by.
We will sort this data A to Z.
I want to copy these formulas Ctrl+C, come out here and paste special values and get rid of duplicates.
There's only one so I can delete the row.
All right, so here's the text in the proper way to sort this.
Sam I can't believe this works.
File >> Options >> Advanced.
Scroll all the way down to the bottom where we can see “Edit Custom Lists” and import that as a custom list.
I'm like there's no way this will work but let me show you how it works.
So we come back and we now select our data set then click on Sort.
I want to sort by “Desired plant date.” Sort on Values, yes but not oldest to newest I want to use custom list and sort by that list, click OK, click OK and it actually does.
It actually sorts the right way.
Okay, so now that we have proof of concept, I'm like well hey Sam that's not going work, custom lists only allow 96 items.
Not true, they allow 254 items.
Well that's not the whole year but it looks like the planting season goes from March 15th, she's in Washington after all maybe to September 1st, who knows.
Let's come back here to our spreadsheet and we're going to enter from the earliest possible date.
Let's even go 3/1/2013 and I'll grab the fill handle and I'll drag down to row 255.
So that's 254 rows and it gets us out to November 9th.
I'm sure Betsy is not planting anything after November 9th, the ground is frozen by then.
So we use our text format here, I don't even have to use the sort by because I already know that it's sorted correctly and we'll copy that and Alt+esv to convert to values.
That range is selected so File>>Options >>Advanced scroll down to the bottom “edit custom lists.” Let's get rid of that tiny list I just put in there.
Delete and import this big list all 254 items.
Do this once on this computer and you will be good to go forever.
Now back in our original data set, we have to use the sort dialog box once “desired plant date” sort on values, custom list is this list.
That's weird why didn't that one go away?
All right click OK and click OK again.
Well that's because it's already-- that's funny, now that I've sorted by that I could actually go in and delete the custom list and now if we add something new next year for example, Dill and this and we do it on 3/29 of 2014 and plant it 3 lineal feet.
Now when I sort I should just be able to click A to Z and it remembers the custom list.
So I can still sort with a single button and we're good to go.
Betsy says “hey while I'm at it, how do I get the grid lines to show back in the green cells?” Yeah those gridlines disappear once you fill the cell even if you fill the cell with white you're going lose the gridlines.
It's any fill that kills the gridlines, so even the white, see it gets rid of the grid lines.
The best way to bring those back, well you're not really bringing them back you're replacing them with a border, just open this little border drop down here and choose all borders and you'll have grid lines that appear.
You know hey if those are too much for you just Ctrl+1 go to the border tab and we can change to light grey and then do inside and out line, click OK and it looks just like it's a regular gridline.
Oh Mike Wow I took six minutes and 40 seconds let me throw it over to you Mike: Thanks MrExcel.
Oh man I'm going give you 3 points.
Actually what I'll do is I'll give you a point, I'll give Sam a point for that awesome custom list and I'm going give a point to Betsy too for showing us that Find and Replace can take these serial numbers and actually replace the 12 with a 13, absolutely awesome.
Now I'm going add an extra helper column and convert this to a table so that formula which is going to be in our helper column will be automatically copied down and then we just have a column we can sort upon.
Now this first row here may interfere with the sorting so I'm going insert, actually I'm going click in a single cell and use Alt+IR to insert a row and then Alt+IC to insert a column.
Now I'm going call this “Sort” and I'll just use the text function.
The text function is great it takes a value which is a serial number comma and we just have to create a custom number format in double quote so I'm going to put mm - dd something like that.
The Formula =TEXT(D4,”mm-dd”) So now it'll show me not the serial number but a text that has 01/01 for January first etc.
So let's see if this works, copy down.
So now I can sort upon this column here and now I'm going to convert it to a table Ctrl+T, single cell right, Ctrl+T and click OK.
So now I have a column here I can sort upon based on these dates over here so just A to Z and there we go.
If I come down here I can either just click below here and say 10, whoops, that one's going to auto populate.
I'm going to say beans here and then we'll say 10/1 or something like that, Row Feet 10 and then whatever else and so there we can see we have auto-populated our formula.
I can come up here and sort A to Z.
Looks like it was already sorted, I could do a Z to A.
All right, throwing it back to MrExcel.
Bill: Hey all right Mike that was good points all around.
Points to Betsy, I love that sorting or doing a find and replace on the year of the dates.
Sam Rad awesome trick with a custom list, Mike great tip with a Ctrl +T table there to make the formula go down also whilst I just deleted Betsy's title I like how you explained how don't need that blank row between the title and Row 1 and the headings in now Row 3.
All right, well I want to thank everyone for stopping by.
We'll see you next week for another dueling Excel podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel.
We will be joined by Mike Girvin from Excel Is Fun.
This is episode 119: Sort by just month and day.
I love this problem.
This is a real-life problem.
Betsy from Olympia, Washington is keeping track of planting dates.
She started the spreadsheet last year and here's how it works.
She puts in the crop types, a variety and hey we planted this on 4/22 and 5 Row feet.
Right, everything's good.
Last year she started this spreadsheet and started again this year and now we actually want to use this spreadsheet all right, to do some planning.
We want to be able to sort this data by desired plant date.
Betsy I got rid of your row one up here because it was --we needed a break between the headings and the title I could have inserted a row I just got rid of the title and we sort ascending and it doesn't work.
Right, here's March, here's May, here's June and here's July.
Back to March, what's going on?
Well Betsy had discovered that in Excel when she enters 4/15, Excel is secretly adding a year.
So the things she entered last year have 2012 and the things she entered this year have 2013.
How do we stop that?
We don't want Excel to add that year or is there some way to sort by month and day?
All right, now Betsy’s solution, which I think is brilliant, I never realized this would work.
We just Ctrl+ H for Find and Replace.
Change area occurrence of 2012 to 2013 and click Replace All and sure enough that actually works.
I can't believe that it works.
All right, so our solution has to be easier than that solution.
I don't know it's going to be but you know I had a couple of ideas.
I had thought maybe a macro would work.
She changed macro that grabs the date 3/15 and puts it back to 1900 all the time or a macro here select these dates hit the icon in the quick access toolbar and it converts everything to the current year, that would be an easy little macro but I'm going to give credit to Sam Rad from the Excel team.
Sam came up with this idea we go back to formatting.
I'm adding this in the way that Betsy wants it formatted, just month and day.
I'm going take that data Ctrl+C, new workbook Ctrl+N and Ctrl+V.
So there's our original and I'm going format this as text.
Does Betsy have to do every single time she wants to sort?
No, this is a one-time thing.
Actually I'm going make it a two -time thing because I need to prove that it works and then we'll do it for real.
So there's the text that we want to display and then I'm going do a Sort By in C2 and this is going be =text(A2,”mmdd”) and we have something to sort by.
We will sort this data A to Z.
I want to copy these formulas Ctrl+C, come out here and paste special values and get rid of duplicates.
There's only one so I can delete the row.
All right, so here's the text in the proper way to sort this.
Sam I can't believe this works.
File >> Options >> Advanced.
Scroll all the way down to the bottom where we can see “Edit Custom Lists” and import that as a custom list.
I'm like there's no way this will work but let me show you how it works.
So we come back and we now select our data set then click on Sort.
I want to sort by “Desired plant date.” Sort on Values, yes but not oldest to newest I want to use custom list and sort by that list, click OK, click OK and it actually does.
It actually sorts the right way.
Okay, so now that we have proof of concept, I'm like well hey Sam that's not going work, custom lists only allow 96 items.
Not true, they allow 254 items.
Well that's not the whole year but it looks like the planting season goes from March 15th, she's in Washington after all maybe to September 1st, who knows.
Let's come back here to our spreadsheet and we're going to enter from the earliest possible date.
Let's even go 3/1/2013 and I'll grab the fill handle and I'll drag down to row 255.
So that's 254 rows and it gets us out to November 9th.
I'm sure Betsy is not planting anything after November 9th, the ground is frozen by then.
So we use our text format here, I don't even have to use the sort by because I already know that it's sorted correctly and we'll copy that and Alt+esv to convert to values.
That range is selected so File>>Options >>Advanced scroll down to the bottom “edit custom lists.” Let's get rid of that tiny list I just put in there.
Delete and import this big list all 254 items.
Do this once on this computer and you will be good to go forever.
Now back in our original data set, we have to use the sort dialog box once “desired plant date” sort on values, custom list is this list.
That's weird why didn't that one go away?
All right click OK and click OK again.
Well that's because it's already-- that's funny, now that I've sorted by that I could actually go in and delete the custom list and now if we add something new next year for example, Dill and this and we do it on 3/29 of 2014 and plant it 3 lineal feet.
Now when I sort I should just be able to click A to Z and it remembers the custom list.
So I can still sort with a single button and we're good to go.
Betsy says “hey while I'm at it, how do I get the grid lines to show back in the green cells?” Yeah those gridlines disappear once you fill the cell even if you fill the cell with white you're going lose the gridlines.
It's any fill that kills the gridlines, so even the white, see it gets rid of the grid lines.
The best way to bring those back, well you're not really bringing them back you're replacing them with a border, just open this little border drop down here and choose all borders and you'll have grid lines that appear.
You know hey if those are too much for you just Ctrl+1 go to the border tab and we can change to light grey and then do inside and out line, click OK and it looks just like it's a regular gridline.
Oh Mike Wow I took six minutes and 40 seconds let me throw it over to you Mike: Thanks MrExcel.
Oh man I'm going give you 3 points.
Actually what I'll do is I'll give you a point, I'll give Sam a point for that awesome custom list and I'm going give a point to Betsy too for showing us that Find and Replace can take these serial numbers and actually replace the 12 with a 13, absolutely awesome.
Now I'm going add an extra helper column and convert this to a table so that formula which is going to be in our helper column will be automatically copied down and then we just have a column we can sort upon.
Now this first row here may interfere with the sorting so I'm going insert, actually I'm going click in a single cell and use Alt+IR to insert a row and then Alt+IC to insert a column.
Now I'm going call this “Sort” and I'll just use the text function.
The text function is great it takes a value which is a serial number comma and we just have to create a custom number format in double quote so I'm going to put mm - dd something like that.
The Formula =TEXT(D4,”mm-dd”) So now it'll show me not the serial number but a text that has 01/01 for January first etc.
So let's see if this works, copy down.
So now I can sort upon this column here and now I'm going to convert it to a table Ctrl+T, single cell right, Ctrl+T and click OK.
So now I have a column here I can sort upon based on these dates over here so just A to Z and there we go.
If I come down here I can either just click below here and say 10, whoops, that one's going to auto populate.
I'm going to say beans here and then we'll say 10/1 or something like that, Row Feet 10 and then whatever else and so there we can see we have auto-populated our formula.
I can come up here and sort A to Z.
Looks like it was already sorted, I could do a Z to A.
All right, throwing it back to MrExcel.
Bill: Hey all right Mike that was good points all around.
Points to Betsy, I love that sorting or doing a find and replace on the year of the dates.
Sam Rad awesome trick with a custom list, Mike great tip with a Ctrl +T table there to make the formula go down also whilst I just deleted Betsy's title I like how you explained how don't need that blank row between the title and Row 1 and the headings in now Row 3.
All right, well I want to thank everyone for stopping by.
We'll see you next week for another dueling Excel podcast from MrExcel and ExcelIsFun.
