Goal is to create sample data with product;product;product;product
Goal is to always have 2 or more products, up to a max of 12
Store a list of products in a custom list so you can easily generate a column of single products
Using RANDBETWEEN() might return duplicate items in the list
Use RAND() function to decide if this product is included or not
Use TEXTJOIN() to concatenate the non-blanks with semi-colons in between
Now that you have one result, how do you make many results
Surprising that one Copy and multiple Paste Values will paste the current result of the formula
Speed the Paste Values by using F4 to Re do
But - a super fast way: Use What-If Tools and a Data Table with an empty cell as the Column Input Cell
Thanks to Professor Simon Benninga for this method
Goal is to always have 2 or more products, up to a max of 12
Store a list of products in a custom list so you can easily generate a column of single products
Using RANDBETWEEN() might return duplicate items in the list
Use RAND() function to decide if this product is included or not
Use TEXTJOIN() to concatenate the non-blanks with semi-colons in between
Now that you have one result, how do you make many results
Surprising that one Copy and multiple Paste Values will paste the current result of the formula
Speed the Paste Values by using F4 to Re do
But - a super fast way: Use What-If Tools and a Data Table with an empty cell as the Column Input Cell
Thanks to Professor Simon Benninga for this method
Transcript of the video:
Learn Excel From MrExcel, Episode 2155: Generate Multiple Random Results From One Formula.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, in addition, to the podcast and writing books, I write a monthly article for Strategic Finance magazine.
I was working on next month's article where I was showing them how to use power query to split a column of ; separated data to rows and I needed to generate some fake data for that, and why I didn't open the file from episode 2097, I have no idea.
I just wanted to create some fake data.
So, anywhere from 2 to 12 products in one cell, and, in the process of doing that, I used a lot of tricks from the podcast -- text join; fill APPLE, BANANA, CHERRY; Random Walk; F4 To Redo -- and I think along the way I discovered some interesting things of how to speed up this process.
Now, first off, it would have been great if I could have just created one huge massive array formula that would have generated that data.
I could have copied that formula down but I couldn't reach my copy of CTRL+SHIFT+ENTER and I was just looking for something simpler that morning.
I'm a big fan of RANDBETWEEN.
I use RANDBETWEEN all of the time.
So, if we had a list of 12 products and then, here, generated a series of answers using RANDBETWEEN, so the index of A1 to A12, asking for a random number from 1 to 12, well every time I press F9, I get a different list of products, and then I want a different number of products in each one, so here, RANDBETWEEN of 2 through 7 or 2 through 12 or whatever the upper or lower limit is, and then using TAXJOIN, that awesome new function in Office 365, separated by ;, ignore the blanks, and then we're going from E2 right there down to anywhere in E2 to E12 -- based on the 6th value in this case -- will generate that list, alright?
But the reason I don't like this is that I kept pressing F9, see, I start getting repeats and, you know, allegedly this is orders coming from a website and why would someone order ELDEBERRY?
ELDERBERRY just doesn't make sense, right?
So, I didn't like the chance there of getting DATE DATE.
I wanted to have a unique list.
So, here's what I decided that I was going to do.
First off, I was going to create a list of the 12 products and I have this memorized as a custom list, so I can just generate a nice alphabetic list of items, and then I wanted anywhere from 2, you know, to about 7 of these, and so what I did here is I say =IF the RAND.
RAND is an awesome function that generates a decimal from 0 to 1 is < .6.
So, in other words in about 60% of the cases, I want you to bring that product over here to column B, otherwise give me nothing “”. I will copy that down.
What that's going to do for me is generate a list of products.
There’ll never be any repeats.
There's no chance of any repeats, and every time I press F9, I get a different list of products, and, yeah, it looks like we're getting, you know, the right number of products each time.
Alright. So, now that we have that, the new function in Excel in Office 365 is TEXTJOIN.
I love this.
The delimiter is going to be a ; and then ignore empty.
It doesn't matter.
I actually…yeah, no, it really doesn’t matter here.
That's the important thing.
We're going to ignore empty.
TRUE, and then here's our list of products like that.
Okay.
So, there we have our product list for the first row, but I need to generate a whole bunch of these, and this is where we actually get to the issue, the issue that I was trying to solve in this particular case.
Now, if I would just copy that formula down, alright, if I took that original formula and came here and edited this -- choose the :, press F4 to make sure that I have an absolute reference, and copy it down -- you'll see that I end up with identical items all the way down.
It's not very interesting fake data, right?
So, that's not going to work.
What I need to do is I need to take the result of this formula and create a whole bunch of them, alright?
So, initially I did this.
I did CONTROL+C, and then I come here and I'm going to do paste special values -- or I guess that's just paste -- and PASTE VALUES like that, alright, and what's fascinating to me -- and I've talked about this once on the podcast before and everyone in the YouTube comments was, well, of course that's going to work; no -- what's fascinating to me is I copied cell C14, and so you would think that, when I copied C14, that text from C14 would have been copied to the clipboard, but it's not.
It's pointing to C14, right?
So, the first time that I paste, I got CHERRY, DATE, ELDERBERRY, but you now see that C14, the marching ants, has changed to APPLE, CHERRY, FIG, and so I’m going to come down here and I'm going to PASTE VALUES again, and I'm always shocked that it changed to the new value.
Alright, so, if I just could just PASTE VALUES, PASTE VALUES, PASTE VALUES, PASTE VALUES, it would generate a new answer every single time.
This time when I PASTE VALUES, APPLE, BANANA, DATE, FIG, ICEBERG, JACKFRUIT, but, look, it's a hassle to grab the mouse and come up here and choose PASTE and choose VALUES.
So, I'm going to use the awesome REDO function -- not UNDO, REDO -- which is F4, so F4, paste the new value.
When I press F4, I’ll get but BANANA, DATE, ELDERBERRY, LIME.
So, it's just simple.
F4, DOWN ARROW, F4, DOWN ARROW, F4, DOWN ARROW, alright, and life is great.
There, I have enough fake data for the article, alright, but even that is a hassle, alright?
So, the method that I learned from a good friend of mine who's now deceased -- professor Simon Benninga taught me this -- if we have a model -- and this is essentially a model -- that is using RAND or RANDBETWEEN and generating a result, what you can do is you can have multiple versions of that result happen, alright, and we have to start from the cell to the left of the result of our model, choose that cell and the cell that contains your formula, and then, however many you want -- let's say I needed a 100 of these or 132 of these -- just copy or select all the way down, and we're going to come here to the DATA tab, the DATA tab, WHAT-IF ANALYSIS, DATA TABLE, alright?
Now, I use this all the time to show how to create multiple scenarios but, in this case, we don't really have anything for ROW INPUT CELL.
For the COLUMN INPUT CELL, just choose any empty cell -- it doesn't matter what cell it is -- and this is going to run this model 132 times, each time essentially pressing F9 to generate new random values like that, click OK, and, bam, and it works.
I absolutely love that.
Now, this is live.
Every time I press F9, I get a new set of 132 of these.
So, just copy -- CONTROL+C -- and come out here, PASTE SPECIAL VALUES, and we have our fake list of products, and we're ready to essentially do what was back in episode 2097: split a column of x;x;x; to rows.
Highly recommend you check out that video, it's a great video, or the November issue, November 2017 issue, of Strategic Finance magazine.
It'll be out online in early November.
Alright.
So, all of these methods are in the book Power Excel With MrExcel, 2017 edition.
Click that i on the top right-hand corner to check it out.
Recap of what we talked about today: the goal is to create a sample data set with ; separated products; always two or more products up to a max of 12; so I stored a list of products in a custom list so you can easily generate a single column of products; using RANDBETWEEN, that would have worked but we have duplicates in the list so I'm using the RAND function to say is this product in or not; then the TEXTJOIN function to concatenate the non-blanks with ;s in between; but then the big question, now that you have one result, how do you make multiple results using the RAND function; well, that's surprising that one copy and then multiple paste values will give you different answers because it's always recalculating after each paste; and you could speed that using F4 to redo the paste values.
Not a bad way to go, but the superfast way, thanks to Professor Benninga, use the WHAT-IF tools and a data table with an empty cell as the COLUMN INPUT CELLS and you'll be able to generate hundreds of random results very, very quickly.
Alright, there you go.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, in addition, to the podcast and writing books, I write a monthly article for Strategic Finance magazine.
I was working on next month's article where I was showing them how to use power query to split a column of ; separated data to rows and I needed to generate some fake data for that, and why I didn't open the file from episode 2097, I have no idea.
I just wanted to create some fake data.
So, anywhere from 2 to 12 products in one cell, and, in the process of doing that, I used a lot of tricks from the podcast -- text join; fill APPLE, BANANA, CHERRY; Random Walk; F4 To Redo -- and I think along the way I discovered some interesting things of how to speed up this process.
Now, first off, it would have been great if I could have just created one huge massive array formula that would have generated that data.
I could have copied that formula down but I couldn't reach my copy of CTRL+SHIFT+ENTER and I was just looking for something simpler that morning.
I'm a big fan of RANDBETWEEN.
I use RANDBETWEEN all of the time.
So, if we had a list of 12 products and then, here, generated a series of answers using RANDBETWEEN, so the index of A1 to A12, asking for a random number from 1 to 12, well every time I press F9, I get a different list of products, and then I want a different number of products in each one, so here, RANDBETWEEN of 2 through 7 or 2 through 12 or whatever the upper or lower limit is, and then using TAXJOIN, that awesome new function in Office 365, separated by ;, ignore the blanks, and then we're going from E2 right there down to anywhere in E2 to E12 -- based on the 6th value in this case -- will generate that list, alright?
But the reason I don't like this is that I kept pressing F9, see, I start getting repeats and, you know, allegedly this is orders coming from a website and why would someone order ELDEBERRY?
ELDERBERRY just doesn't make sense, right?
So, I didn't like the chance there of getting DATE DATE.
I wanted to have a unique list.
So, here's what I decided that I was going to do.
First off, I was going to create a list of the 12 products and I have this memorized as a custom list, so I can just generate a nice alphabetic list of items, and then I wanted anywhere from 2, you know, to about 7 of these, and so what I did here is I say =IF the RAND.
RAND is an awesome function that generates a decimal from 0 to 1 is < .6.
So, in other words in about 60% of the cases, I want you to bring that product over here to column B, otherwise give me nothing “”. I will copy that down.
What that's going to do for me is generate a list of products.
There’ll never be any repeats.
There's no chance of any repeats, and every time I press F9, I get a different list of products, and, yeah, it looks like we're getting, you know, the right number of products each time.
Alright. So, now that we have that, the new function in Excel in Office 365 is TEXTJOIN.
I love this.
The delimiter is going to be a ; and then ignore empty.
It doesn't matter.
I actually…yeah, no, it really doesn’t matter here.
That's the important thing.
We're going to ignore empty.
TRUE, and then here's our list of products like that.
Okay.
So, there we have our product list for the first row, but I need to generate a whole bunch of these, and this is where we actually get to the issue, the issue that I was trying to solve in this particular case.
Now, if I would just copy that formula down, alright, if I took that original formula and came here and edited this -- choose the :, press F4 to make sure that I have an absolute reference, and copy it down -- you'll see that I end up with identical items all the way down.
It's not very interesting fake data, right?
So, that's not going to work.
What I need to do is I need to take the result of this formula and create a whole bunch of them, alright?
So, initially I did this.
I did CONTROL+C, and then I come here and I'm going to do paste special values -- or I guess that's just paste -- and PASTE VALUES like that, alright, and what's fascinating to me -- and I've talked about this once on the podcast before and everyone in the YouTube comments was, well, of course that's going to work; no -- what's fascinating to me is I copied cell C14, and so you would think that, when I copied C14, that text from C14 would have been copied to the clipboard, but it's not.
It's pointing to C14, right?
So, the first time that I paste, I got CHERRY, DATE, ELDERBERRY, but you now see that C14, the marching ants, has changed to APPLE, CHERRY, FIG, and so I’m going to come down here and I'm going to PASTE VALUES again, and I'm always shocked that it changed to the new value.
Alright, so, if I just could just PASTE VALUES, PASTE VALUES, PASTE VALUES, PASTE VALUES, it would generate a new answer every single time.
This time when I PASTE VALUES, APPLE, BANANA, DATE, FIG, ICEBERG, JACKFRUIT, but, look, it's a hassle to grab the mouse and come up here and choose PASTE and choose VALUES.
So, I'm going to use the awesome REDO function -- not UNDO, REDO -- which is F4, so F4, paste the new value.
When I press F4, I’ll get but BANANA, DATE, ELDERBERRY, LIME.
So, it's just simple.
F4, DOWN ARROW, F4, DOWN ARROW, F4, DOWN ARROW, alright, and life is great.
There, I have enough fake data for the article, alright, but even that is a hassle, alright?
So, the method that I learned from a good friend of mine who's now deceased -- professor Simon Benninga taught me this -- if we have a model -- and this is essentially a model -- that is using RAND or RANDBETWEEN and generating a result, what you can do is you can have multiple versions of that result happen, alright, and we have to start from the cell to the left of the result of our model, choose that cell and the cell that contains your formula, and then, however many you want -- let's say I needed a 100 of these or 132 of these -- just copy or select all the way down, and we're going to come here to the DATA tab, the DATA tab, WHAT-IF ANALYSIS, DATA TABLE, alright?
Now, I use this all the time to show how to create multiple scenarios but, in this case, we don't really have anything for ROW INPUT CELL.
For the COLUMN INPUT CELL, just choose any empty cell -- it doesn't matter what cell it is -- and this is going to run this model 132 times, each time essentially pressing F9 to generate new random values like that, click OK, and, bam, and it works.
I absolutely love that.
Now, this is live.
Every time I press F9, I get a new set of 132 of these.
So, just copy -- CONTROL+C -- and come out here, PASTE SPECIAL VALUES, and we have our fake list of products, and we're ready to essentially do what was back in episode 2097: split a column of x;x;x; to rows.
Highly recommend you check out that video, it's a great video, or the November issue, November 2017 issue, of Strategic Finance magazine.
It'll be out online in early November.
Alright.
So, all of these methods are in the book Power Excel With MrExcel, 2017 edition.
Click that i on the top right-hand corner to check it out.
Recap of what we talked about today: the goal is to create a sample data set with ; separated products; always two or more products up to a max of 12; so I stored a list of products in a custom list so you can easily generate a single column of products; using RANDBETWEEN, that would have worked but we have duplicates in the list so I'm using the RAND function to say is this product in or not; then the TEXTJOIN function to concatenate the non-blanks with ;s in between; but then the big question, now that you have one result, how do you make multiple results using the RAND function; well, that's surprising that one copy and then multiple paste values will give you different answers because it's always recalculating after each paste; and you could speed that using F4 to redo the paste values.
Not a bad way to go, but the superfast way, thanks to Professor Benninga, use the WHAT-IF tools and a data table with an empty cell as the COLUMN INPUT CELLS and you'll be able to generate hundreds of random results very, very quickly.
Alright, there you go.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
