Need a formula to pick a random value from a list of values

[csh8428]

I have to dummy up some data for a bunch of fields. Each field has a list of valid values. I've seen workflows on how to do this for one field using unique ids and such, but that's too much to multiply for every field. I'm looking for a formula that I can use. Is there a function that says "pick a value from this list of strings"?

For example, let's say I have 2 columns: [Car] and [Color]. The values to pick from are such: [Car]: Ferrari, McLaren, Lambo. [Color]: Red, White, Yellow

For every  record I want to randomly generate the value for each column with the corresponding list of values.

Thanks for any help!

Craig

[rzdodson]

@csh8428 unsure what you mean by the last line in your previous post. Can you mock up your expected results?

[csh8428]

@rzdodson 

Here's  a sample of input vs output. I'm trying to make [Car] and [Color] formula fields that select a random value from the list for each column. I know how to do this setting up lists with record IDs, then picking a random record ID and appending/joining. But, I have a lot of fields to do this to and it would be much easier/less complex if this is possible using a formula.

Input

ID

ABC

DEF

GHI

Output:

ID	Car(random value from: "Ferrari, McClaren,Lambo"	Color(random value from: "Red, White, Yellow")
ABC	McLaren	White
DEF	Ferrari	Yellow
GHI	Ferrari	Red

[rzdodson]

@csh8428 would you mind posting the workflow you are operating from?

It appears that you are wanting to generate a randomized list predicated off a number of permutations from a series of fields. There could be an infinite number of permutations so it would be helpful to see the extent of the problem set we are trying to solve for.

As an aside, given the potential complexity of your workflow, I would not suggest going down a strictly Formula-tool driven route. The syntax would be difficult to track, and even more difficult to turn over to another developer if you were transitioning the workflow, in my opinion. Instead, I would recommend exploring an iterative macro approach after you have transposed the fields you are needing to permute; then, from there, returning random N records. 

Barring publishing additional sample data, I'll work out an initial solution based on my thoughts above soon.

[csh8428]

I'm not trying to create a list. I have the list for each field. That is already pre-set. Let's say I have 10 fields. Each field has it's own list of 4 possible values to pick randomly from. I want each row to pick a random value from that specific field's list. None of the values are based on any logic or any other fields. To put it more simply: Is there a formula to pick a random value from a list of pre-populated values... like this PickRandomString("Red","Green","Blue"). The function would randomly pick between Red, Green, or Blue. It's the same premise of  the excel RANDBETWEEN function except that I'm doing it with a string of pre-set values instead of numbers.

[rzdodson]

BLUF: No, there is not. There is a RandInt function that you can use for integer-based values; but, there is not an equivalent for string fields that I know of.

You can utilize RandInt and GetWord functions to create your desired output. If you are wanting to sequence through each field, you can transpose the data so that your target field names are now vertically oriented, iterate through each target field, return the random value, then Cross-tab the final output to create the randomized values for each field.

[nagakavyasri]

Try this:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Random-value-generation-from-a...

[csh8428]

Yeah.. That's the part I already new how to do and was hoping there was a pre-defined singular function.

[getcarsvalue]

I can help you out with that! If you're looking to randomly select a value from your list of cars in Excel, you're on the right track with the RANDBETWEEN function. However, let me guide you through the steps to make it work seamlessly:

Create a Helper Column: Start by adding a helper column next to your list of cars. Let's say your car list is in column A, you can use column B for the helper column.

Generate Random Numbers: In the first cell of your helper column (let's say B2), use the formula:

excel

=RANDBETWEEN(1,COUNTA(A:A))

This formula generates a random number between 1 and the total count of your cars.

Sort the List: Now, sort your entire table (both the list of cars and the helper column) based on the helper column (column B). This will effectively shuffle your list randomly.

Get the Random Car Value: The first value in your now-shuffled list will be the randomly selected car. You can reference this cell wherever you need the random car value.

Refresh as Needed: Whenever you want a new random car value , you can recalculate the sheet or refresh, and the order will change, giving you a different car each time.

Remember to adjust the references in the formulas based on the actual location of your data.

Give it a try, and let me know if you encounter any issues or if you have any specific requirements!

https://getcarsvalue.com/

[CoG]

No such function exists to my knowledge, but here is a workflow that randomly select values across multiple columns:

This can be put into a macro if you want to involve "less tools" for your use case. This workflow works for any number of columns by the way.