While working with a large database, you may need to create a list of data or text from the given database. If you do it manually, it will cost you a great deal of time. Excel has some features and formulas by which you can easily make your list from the data range. Today in this article, we will demonstrate some methods to create lists from range in excel.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Create List from Range in Excel (3 Methods)
1. Creating List from Range by Making a Drop-Down List
Making a drop-down list is a great way to create lists from a given range. Using a drop-down list you can select any specific data in seconds. Here in this section, we will discuss two types of drop-down lists while making a list from a range. To know more about the drop-down list, Click Here!
i. Independent Drop-Down List
An Independent drop-down list is where you can choose your data freely and there is no auto-update of the data. Let’s learn!
Here in the given situation, some names of the books and movies are given in the “Book Name” and the “Movie Name” column. We need to make a drop-down list from this range of data. To do this, create two columns named “Book List” and “Movie List” anywhere in the worksheet. Under those columns, we will make our drop-down list.
Now click on cell E4 under the Book List column, go to Data then click on Data Validation.
A data validation window appears. Choose List as the data validation criteria, click on the Source Field Icon to select your data source.
Select your data from the Book Name column ($B$4:$B$16), and click OK to continue
And our drop-down list from the Book Name data list is created.
Similarly, for the Movie List column, repeat the following procedures and in the source field, select your data from the Movie Name column ($C$4:$C$16).
Click Ok to get your drop-down list. That’s how, by creating a drop-down list, you can make list from a data range.
ii. Dynamic Drop-Down List
A dynamic drop-down list will auto-update your data. To make a dynamic drop-down list, go to Data, click on Data Validation. In the data validation window, choose List as validation criteria. The in the source field, insert the OFFSET function. The formula is this,
- Reference is $B$4
- Rows and Columns is 0
- [height] is COUNTIF($B$4:$B$100,”<>”)
Click Ok to continue. Our dynamic drop-down list is created.
To check if this is list is dynamic or not, delete some data from our data range. Then we will see that the drop-down list data is also updated.
Now for the Movie list, do the same as we demonstrated for the Book Name list. And for this case the OFFSET formula is,
Click Ok to make your dynamic drop-down list from a given range.
2. Create List from Range Using Excel VBA
Using the Excel VBA, we can easily make list from a given range of data. To learn it let’s follow these procedures.
We will use the same data sheet for this procedure. First, press CTRL+F11 to open the developer window.
A new window appears. Here Right-Click on the sheet where you want to apply the VBA code. From the available options, select Insert, click on Module.
In the new window, write down your VBA code. We are providing the code here.
Sub UniqueList() 'VBA Dim InputRng As Range, OutRng As Range xTitleId = "Book & Movie Name" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range:", xTitleId, InputRng.Address, Type:=8) Set OutRng = Application.InputBox("OutPut to (single cell):", xTitleId, Type:=8) For i = 1 To InputRng.Rows.Count For j = 1 To InputRng.Columns.Count OutRng.Value = InputRng.Cells(i, j).Value Set OutRng = OutRng.Offset(1, 0) Next Next End Sub
You can change the Inputting range as per your requirement. Click on the Run icon.
A new window popped out. Here input the range of data that you will create a list with. Click on Ok to continue.
Now in the output window select the cell where you want to get your list.
Click Ok to get your list. And our job is done.
3. Create List from Range Based on a Criteria
In this section, we will create a list from a data range based on criteria.
In the following example, we are given some book and movie names in the Book And Movie Name column. In the column, some names are repeated. Now we will make a unique list from this column where each name will appear only once.
In cell D4 under the Unique List column, Apply the INDEX with the MATCH formula. Insert the values and the final formula is,
- List is B4:B24
- Look_Value for MATCH function is 0
- The range for the COUNTIF function is $D$3:D3
- Criterion is B4:B24
- We want the EXACT match (0).
This formula is an array formula. So, press “CTRL+SHIFT+ENTER” to apply this formula
Now apply the same formula to the rest of the cells to get the final result.
➤To avoid errors, remember to check Ignore Blank and In-cell Dropdown.
➤While creating a dynamic drop-down list, Make sure that the cell references are absolute (such as $B$4) and not relative (such as B2, or B$2, or $B2)
➤ Press CTRL+SHIFT+ENTER to apply an array formula.
Today we discussed three different procedures to create a list from the given range. Hope this article proves useful to you. If you have any suggestions or confusion, you are most welcome to comment.
Similar Articles for You to Explore
- How to Create Dynamic List in Excel Based on Criteria (Single and Multiple Criteria)
- How to Generate List Based on Criteria in Excel
- How to Create Drop-Down List in Multiple Columns in Excel (3 Ways)
- Auto Update Drop Down List in Excel (3 Ways)
- How to Make a Drop Down List in Excel (Independent and Dependent)
- How to use IF Statement to Create a Drop-Down List in Excel