Choose random weeks from the Data Set, but do it without repeating a week. In a revisit to Episode #1469, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #1471 today to learn the answer and solve the challenge.
Learn Excel 2010 -- "Copy Totals to Week # Macro": Podcast #1470
November 14, 2011 at 1:06 pm MrExcel East Edit
Patrick has a weekly Expense Ledger Spreadsheet set up; he wants to create a Macro to copy the Totals from Report 1 to a corresponding Week Number Row on the Summary worksheet. This Macro has to use the FIND command in VBA to figure out where to paste special the data. Follow along with Episode #1470 as Bill shows us how to accomplish the task.
...This blog is the video podcast companion to the book, 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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Learn Excel 2010 -- "Copy Totals to Week # Macro": Podcast #1470
November 14, 2011 at 1:06 pm MrExcel East Edit
Patrick has a weekly Expense Ledger Spreadsheet set up; he wants to create a Macro to copy the Totals from Report 1 to a corresponding Week Number Row on the Summary worksheet. This Macro has to use the FIND command in VBA to figure out where to paste special the data. Follow along with Episode #1470 as Bill shows us how to accomplish the task.
...This blog is the video podcast companion to the book, 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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
This is a doozy Learn Excel podcast, episode 1471: random no repeats.
I did that last week.
I did that random podcast and that repeat came up the first time.
Then the question comes in, “Hey I need-- I need it without repeats”.
I hate that.
We need-- from these twelve months here we need to choose six months randomly.
No repeat.
Okay, here's my method. This would actually be a good one to do with Mike because there's probably a million ways to do this.
I put the RAND function out there on the left-hand side.
Now, watch this.
I'm going to ask for =LARGE of this array, comma-- and right here I want the largest one, but rather than type the number one, there I'm going to use that great trick row of A1.
That is the geekiest way to write the number one.
I will copy that down.
They said they needed six months randomly.
If they needed three months, I would only copy it down three.
All right, so, there are the largest six numbers from there.
Then, our favorite, favorite function =VLOOKUP of this, comma, that range, F4, comma, 2, comma, false.
Yes, all right.
Copy that down.
All right, now, every time I press F9, I'm going to randomly get six months and there will-- I’m going to use the word never, be a repeat.
Never.
There's a footnote there about never.
There will actually be a repeat.
If I did this-- if I pressed F9 100,000 times, I would eventually get one where randomly I ended up with the exact same digits out here, but the odds are just stacked against it.
It might happen once.
If it happens, just press F9 and it'll be another year before it happens again.
So, cool way to go virtually assured that you're going to have no repeats in this particular method.
Okay, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
This is a doozy Learn Excel podcast, episode 1471: random no repeats.
I did that last week.
I did that random podcast and that repeat came up the first time.
Then the question comes in, “Hey I need-- I need it without repeats”.
I hate that.
We need-- from these twelve months here we need to choose six months randomly.
No repeat.
Okay, here's my method. This would actually be a good one to do with Mike because there's probably a million ways to do this.
I put the RAND function out there on the left-hand side.
Now, watch this.
I'm going to ask for =LARGE of this array, comma-- and right here I want the largest one, but rather than type the number one, there I'm going to use that great trick row of A1.
That is the geekiest way to write the number one.
I will copy that down.
They said they needed six months randomly.
If they needed three months, I would only copy it down three.
All right, so, there are the largest six numbers from there.
Then, our favorite, favorite function =VLOOKUP of this, comma, that range, F4, comma, 2, comma, false.
Yes, all right.
Copy that down.
All right, now, every time I press F9, I'm going to randomly get six months and there will-- I’m going to use the word never, be a repeat.
Never.
There's a footnote there about never.
There will actually be a repeat.
If I did this-- if I pressed F9 100,000 times, I would eventually get one where randomly I ended up with the exact same digits out here, but the odds are just stacked against it.
It might happen once.
If it happens, just press F9 and it'll be another year before it happens again.
So, cool way to go virtually assured that you're going to have no repeats in this particular method.
Okay, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
