Đề Xuất 3/2023 # How To Select Random Names From A List In Excel? # Top 5 Like

Cập nhật nội dung chi tiết về How To Select Random Names From A List In Excel? mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

How to select random names from a list in Excel?

Supposing you have a list of names locates in column A in Excel, for quickly selecting random name from this list, what would you do? Actually, you can select random name from list with formula. Beside the formula method, we will show you a handy add-in to easily solve this problem.

Select random name from a list with formula Easily select random name from a list with Kutools for Excel

Select random name from a list with formula

Please do as follows to select random name from a list in Excel.

1. Select a blank cell besides the list, copy and paste formula =INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1) into the Formula Bar, and then press the Enter key. You can see a random name is displayed in the selected cell.

Notes:

1. Select this cell and press F9 key, you will get different names randomly.

2. You can also select the cell and drag the fill handle down to list the random names you need.

3. The random name will be changed automatically every time you refresh the worksheet.

Select random name from a list with Kutools for Excel

With the Sort Range Randomly utility of Kutools for Excel, you can quickly select random names from list in Excel.

Before applying Kutools for Excel, please download and install it firstly.

2. In the Sort/Select Range Randomly dialog box and under the Select tab, please configure as follows.

Specify number of cells you want to select in the No. of cells to select box;

Choose Select random cells option in the Select Type section;

Now the specified number of cells in the name list are selected randomly.

Note: If you want to list the randomly selected names together, please copy and paste them to other cells.

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Easily select random name from a list with Kutools for Excel

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…

More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.

Open and create multiple documents in new tabs of the same window, rather than in new windows.

Excel Formula: Random List Of Names

At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this:

INDEX

(

names

)

However, the trick in this case is that we don’t want a single name at a known location, we want 10 random names at unknown locations between 1 and 100. This is an excellent use case for the RANDARRAY function, which can create a random set of integers in a given range. Working from the inside out, we use RANDARRAY to get 10 random numbers between 1 and 100 like this:

RANDARRAY

(

COUNTA

(

names

)

The COUNTA function is used to get a dynamic count of names in the list, but we could replace COUNTA with a hardcoded 100 in this case with the same result:

INDEX

(

names

RANDARRAY

(

100

TRUE

))

In either case, RANDARRAY will return 10 numbers in an array that looks like this:

{

;

}

Note: these numbers are random only and do not map directly to the example shown.

This array is returned directly to the INDEX function as the row argument:

INDEX

(

names

{

;

}

Because we are giving INDEX 10 row numbers, it will 10 results, each corresponding to a name at the given position. The 10 random names are returned in a spill range beginning in cell D5.

Prevent duplicates

One problem with the above formula (depending on your needs) is that RANDARRAY will sometimes generate duplicate numbers. In other words, there is no guarantee that RANDARRAY will return 10 unique numbers.

To ensure 10 different names from the list, you can adapt the formula to randomly sort the full list of names, then retrieve the first 10 names from the list. The formula in F5 uses this approach:

INDEX

(

SORTBY

(

names

RANDARRAY

(

COUNTA

(

names

))),

SEQUENCE

(

))

The approach here is the same as above – we are using INDEX to retrieve 10 values from the list of names. However, in this version of the formula, we are sorting the list of names randomly before handing giving the list to INDEX like this:

SORTBY

(

names

RANDARRAY

(

COUNTA

(

names

)))

Here, the SORTBY function is used to sort the list of names randomly with an array values created by the RANDARRAY function, as explained in more detail here.

Finally, we need to retrieve 10 values. Because we already have names in a random order, we can simply request the first 10 with an array created by the SEQUENCE function like this:

SEQUENCE

(

)

SEQUENCE builds an array of sequential numbers:

{

;

}

which is returned to the INDEX function as the row argument. INDEX then returns the first 10 names in a spill range like the original formula.

How To Clear Recent Document Lists From Excel?

How to clear recent document lists from Excel?

The recent opened workbook lists will be stored in the Recent pane when we open an Excel file each time as following screenshot shown. But, sometimes, you need to clear such long boring workbook lists from the Recent pane. This article will show you how to make the recent document lists disappear.

Clear the recent document lists with Options feature Clear the recent document lists with Clear unpinned Workbooks function Clear the recent document lists with VBA code

Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.

More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.

Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.

Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.

Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.

More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Clear the recent document lists with Options feature

To remove the recent document lists in Excel, you can go to the Excel Options to do a little setting. Please do as follows:

Note: If you want to restore the recent items, you can type the number back to the Show this number of Recent Documents text box in the Excel Options dialog.

Clear the recent document lists with Clear unpinned Workbooks function

You can also remove all the recent document lists by applying the Clear unpinned Workbooks feature.

Notes:

1. After removing the recent document lists, they are not restored any more, and this method is only applied to Excel 2010, 2013.

2. This method only remove the unpinned files.

Clear the recent document lists with VBA code

The following VBA code can help you to remove all the recent document lists immediately as well.

1. Press the ALT + F11 keys together, and it opens the Microsoft Visual Basic for Applications window.

VBA code: remove the recent document lists

Sub Clearrecentfiles() 'Update 20140925 On Error Resume Next Application.RecentFiles.Item(1).Delete Loop End Sub

3. Then press F5 key to run this code, and all the recent document lists have been removed at once.

Note: With this code, the recent document lists cannot be restored.

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.

Open and create multiple documents in new tabs of the same window, rather than in new windows.

How To Find Column Index Number From Table In Excel

Scenario:

Many times when working with long scattered data, we need to first clean data before working on it. It takes time and you just wanted to extract some queries. To extract data, you generally use the VLOOKUP function . But when we have long data with many column fields, then it gets messy because VLOOKUP function requires the correct column index as number, or else the formula returns error. Right there, below is the formula explanation for this kind of problem.

How to solve the problem?

For this we will use the MATCH function . If you haven’t heard of this function, get used to it. It is an excel lookup function which returns the Index of the lookup value in the array. Here we need to get Index number of the column name. Then we will proceed to further step How to lookup values in tables using the MATCH function. Below is the Generic formula

Generic formula:

column_name : lookup value

table_header : table header array

0 : lookup for exact match

Example :

All of these might be confusing to understand. Let’s understand the formula with explanation and example. Here we have a data table in Sheet 1 ($A$1:$U$9995). So we have the table header as A1:U1 (The first row of the table).

Use the formula

Explanation:

MATCH function looks up value in C4 cell “Order ID”

Sheet1!$A$1:$U$1 is the lookup array argument.

0 argument is given to look up for exact match.

Use the formula.

MATCH index in VLOOKUP function:

Now we have the solution for, how to get the column index of the table. We can use the formula as input to the VLOOKUP function. For example, we need the product name bought by the customer name “Pete Kriz”.

Use the formula:

Note: make sure lookup value in D10 (Pete Kriz) must be in the first column of the table.

Here are all the observational notes regarding using the formula.

Notes:

The formula works for text and numbers both.

The function returns the #NA error if the lookup array argument to the MATCH function is not of the same length of the table array.

The formula returns an error if lookup_value doesn’t match the value in the table lookup_array.

The function matches the exact value as the match type argument to the MATCH function is 0.

Use the argument -1 for less than, 0 for exact match and 1 for the greater than the lookup match.

The lookup values can be given as cell reference or directly using quote symbol ( ” ) in the formula as arguments.

Hope you understood How to extract the column index from the table in Excel. Explore more articles on Excel lookup value & Excel 2019 functions here. If you liked our blogs, share it with your friends on . And also you can follow us on and . We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com .