As we hit this milestone, you can win one of a treasure trove of prizes, including a video iPod, great books from QUE, and more. There are over 40 prizes in all. Also in Episode 500, I draw the winner for the March/April challenge of the month, using a combination of Advanced Filter, INDEX, and RANDBETWEEN. I will use a similar method to award the prizes from our podcast #500 giveaway.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
This is episode number 500.
I want to take an opportunity to thank Leo Laporte.
If we go back to the summer 2005, I was flying up to Toronto every month to do a couple of shows for, Call for Help.
And I was actually flying home from Toronto and the fella next to me at the bar was from Los Angeles and he said oh Leo Laporte, we love his radio show.
Did you know, that Leo's twit podcast is the number one podcast on itunes and I have to admit, I didn't understand a single word in that sentence.
I didn't know what twit was.
I didn't know what a podcast was.
I had never heard of itunes.
But the next time I was in Toronto, I asked Leo about it and Leo is the one who said, "Bill, you really should do an Excel podcast and over the next couple of months.
He helped me out.
Show me what to do.
We came up with the idea that it should be a two-minute daily video podcast and now here we are at episode number 500.
Now, my favorite Leo podcast is Leo and **** Debartolo.
The daily giz-wiz and whenever they reach a milestone like an even number 100 podcast.
**** calls on his friends and they have a whole bunch of cool stuff to give away.
So, I went to both of my publishers and Q, very nicely through in an iPod pedia, a Google Pedia and Q came through with ten great prizes.
First two prizes are a bundle of five books.
They are also giving us an iPod pedia, a Google pedia, special edition using Excel, Holly Macro books through in a couple of power Excel bundles, several learn Excel books.
Me personally, I'm going to throw in a 30gigabyte video iPod.
Brand new from the Apple store, in all we have 40 prizes.
Very easy contest to win.
All you need to do is send your name and postal address to 500 at MrExcel.com and honor the 500th episode.
Let's do that by Monday, May 7th.
Midnight Eastern Time.
Get me your name and postal address.
Please only enter once.
If we have less than 40 people enter, then you all win.
If more than 40 people enter, we'll have to end up using rand between and figure out the order of the prizes.
I'm using some sort of a random number scheme.
Now, for today's podcast coincidentally, we have the challenge of the month from March and April which was best receive challenge ever.
Over 500 entries were set in.
So, let's figure out how we're going to choose the winner of those 500 entries.
We went through and 407 of them had the correct formula.
Now, it's interesting that in those 407 entries, there were over 360 different approaches.
I always say there's always two, three, four ways to do things in Excel.
But for that particular commission challenge, over 350 different formulas that worked came in which is astounding.
I'm going to write up four or five of the best ones and get them to you, okay!
So, we we have this list of 407 names and I noticed that a few people send in multiple entries they send in different formulas.
And I want to eliminate those, so that way everyone's in there once and to do that.
We'll use data, filter, advanced filter.
So, we choose one cell in our data set data, filter, advanced filter.
Say that we want to copy it to another location, specify that's going to go to C3 and say that we want unique records only and just that quickly Excel will give us just the unique 362 names in the original list.
Now, I'm going to use the index function here.
The index function equal index of C4 to 365 and then ask for a particular row number and so the way this works is if we chose row number one, it would be that person.
Row number two, row number three, row number four.
Now to come up with our winner.
We're going to use the rand between function, equal rand between and ask for 1 through 362.
Rand between is easier to use than the old Rand function.
But you have to have the analysis tool-pack turn on.
Alright! So, when we hit [ enter ] here.
We'll have our winner of the power Excel bundle for the March-April challenge.
Here we go.
There it is, it's Peter Stikker.
So, congratulations to Peter.
I'll be dropping them an email, figure out where does ship his power Excel bundle and for everyone who didn't win.
Please feel free to enter our contest.
We have over 40 great prizes to give away.
To send me your name and postal address to 500@MrExcel.com I will probably run through a list of the winners, in a podcast next week.
Hey! Thanks for stopping by, we'll see you tomorrow for another podcast number 501 from MrExcel.
I'm Bill Jelen.
This is episode number 500.
I want to take an opportunity to thank Leo Laporte.
If we go back to the summer 2005, I was flying up to Toronto every month to do a couple of shows for, Call for Help.
And I was actually flying home from Toronto and the fella next to me at the bar was from Los Angeles and he said oh Leo Laporte, we love his radio show.
Did you know, that Leo's twit podcast is the number one podcast on itunes and I have to admit, I didn't understand a single word in that sentence.
I didn't know what twit was.
I didn't know what a podcast was.
I had never heard of itunes.
But the next time I was in Toronto, I asked Leo about it and Leo is the one who said, "Bill, you really should do an Excel podcast and over the next couple of months.
He helped me out.
Show me what to do.
We came up with the idea that it should be a two-minute daily video podcast and now here we are at episode number 500.
Now, my favorite Leo podcast is Leo and **** Debartolo.
The daily giz-wiz and whenever they reach a milestone like an even number 100 podcast.
**** calls on his friends and they have a whole bunch of cool stuff to give away.
So, I went to both of my publishers and Q, very nicely through in an iPod pedia, a Google Pedia and Q came through with ten great prizes.
First two prizes are a bundle of five books.
They are also giving us an iPod pedia, a Google pedia, special edition using Excel, Holly Macro books through in a couple of power Excel bundles, several learn Excel books.
Me personally, I'm going to throw in a 30gigabyte video iPod.
Brand new from the Apple store, in all we have 40 prizes.
Very easy contest to win.
All you need to do is send your name and postal address to 500 at MrExcel.com and honor the 500th episode.
Let's do that by Monday, May 7th.
Midnight Eastern Time.
Get me your name and postal address.
Please only enter once.
If we have less than 40 people enter, then you all win.
If more than 40 people enter, we'll have to end up using rand between and figure out the order of the prizes.
I'm using some sort of a random number scheme.
Now, for today's podcast coincidentally, we have the challenge of the month from March and April which was best receive challenge ever.
Over 500 entries were set in.
So, let's figure out how we're going to choose the winner of those 500 entries.
We went through and 407 of them had the correct formula.
Now, it's interesting that in those 407 entries, there were over 360 different approaches.
I always say there's always two, three, four ways to do things in Excel.
But for that particular commission challenge, over 350 different formulas that worked came in which is astounding.
I'm going to write up four or five of the best ones and get them to you, okay!
So, we we have this list of 407 names and I noticed that a few people send in multiple entries they send in different formulas.
And I want to eliminate those, so that way everyone's in there once and to do that.
We'll use data, filter, advanced filter.
So, we choose one cell in our data set data, filter, advanced filter.
Say that we want to copy it to another location, specify that's going to go to C3 and say that we want unique records only and just that quickly Excel will give us just the unique 362 names in the original list.
Now, I'm going to use the index function here.
The index function equal index of C4 to 365 and then ask for a particular row number and so the way this works is if we chose row number one, it would be that person.
Row number two, row number three, row number four.
Now to come up with our winner.
We're going to use the rand between function, equal rand between and ask for 1 through 362.
Rand between is easier to use than the old Rand function.
But you have to have the analysis tool-pack turn on.
Alright! So, when we hit [ enter ] here.
We'll have our winner of the power Excel bundle for the March-April challenge.
Here we go.
There it is, it's Peter Stikker.
So, congratulations to Peter.
I'll be dropping them an email, figure out where does ship his power Excel bundle and for everyone who didn't win.
Please feel free to enter our contest.
We have over 40 great prizes to give away.
To send me your name and postal address to 500@MrExcel.com I will probably run through a list of the winners, in a podcast next week.
Hey! Thanks for stopping by, we'll see you tomorrow for another podcast number 501 from MrExcel.
