8/12/2023 0 Comments Random list generator in excel![]() ![]() ![]() Column A isn’t really a unique “TV character ID” since that “ID” changes all the time with the RAND function. This is not the usual way you might use VLOOKUP because you’re usually using VLOOKUP with some unique identifier as the lookup value. So in column E, we just do a VLOOKUP using column D as our lookup value and columns A:B as our lookup table to get the name associated with the random number in column D: What does this column of sorted random numbers do for us? Well, we know that each random number in this sorted column corresponds to one of the numbers in column A where we generated the random number. The formula kind of “spills” down for you as your list grows so you don’t have to worry about dragging the formula down until the last row in your data set. This is a relatively new function in Excel since it kind of acts like dynamic array formulas or array-entered formulas. The nice thing about the SORT function is that it automatically fills the formula down to the bottom of your data set. You can also put 0 to indicate false which is what I did in this example to sort in descending order. The SORT function takes in a range of cells as the first parameter, the sort index as the 2nd (which is just the number column we ant to sort on, column #1), and then true or false for sorting in ascending or descending order. I put a space in column C so in cell D2, you enter this formula: ![]() This means if you sort this list of random numbers, the sorted list will change too. It sounds kind of weird, why would you sort a random list of numbers? What does that even mean? As you have probably seen, every time you refresh your Google Sheet or commit an Excel formula by hitting ENTER, all those random numbers in column A will change. Doesn’t look that useful now, but this random number column will drive the rest of the tool to generate your list of random names: Sort this random list of numbers You’ll get a decimal with random numbers. In column A, you put the RAND function and copy it all the way down to the bottom of our list. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |