How to Create List from Range in Excel (3 Methods)

Create List from Range Using Excel VBA

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!

Step-1:

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.

Creating List from Range by an Independent Drop Down List

Step-2:

Now click on cell E4 under the Book List column, go to Data then click on Data Validation.

E4→Data→Data Validation

Creating List from Range by an Independent Drop Down List

Step-3:

A data validation window appears. Choose List as the data validation criteria, click on the Source Field Icon Make List from Range in excelto select your data source.

Creating List from Range by an Independent Drop Down List

Select your data from the Book Name column ($B$4:$B$16), and click OK to continue

Creating List from Range by an Independent Drop Down List

And our drop-down list from the Book Name data list is created.

Creating List from Range by an Independent Drop Down List

Step-4:

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).

Creating List from Range by an Independent Drop Down List

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.

Creating List from Range by an Independent Drop Down List

ii. Dynamic Drop-Down List

Step-1:

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,

=OFFSET($B$4,0,0,COUNTIF($B$4:$B$100,”<>”))

Where,

  • Reference is $B$4
  • Rows and Columns is 0
  • [height] is COUNTIF($B$4:$B$100,”<>”)

Creating List from Range by Dynamic Drop Down List

Click Ok to continue. Our dynamic drop-down list is created.

Creating List from Range by Dynamic Drop Down List

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.

Creating List from Range by Dynamic Drop Down List

Step-2:

Now for the Movie list, do the same as we demonstrated for the Book Name list. And for this case the OFFSET formula is,

=OFFSET($C$4,0,0,COUNTIF($C$4:$C$100,”<>”)

Creating List from Range by Dynamic Drop Down List

Click Ok to make your dynamic drop-down list from a given range.

Creating List from Range by Dynamic Drop Down List

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.

Step-1:

We will use the same data sheet for this procedure. First, press CTRL+F11 to open the developer window.

Create List from Range Using Excel VBA

Step-2:

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.

Create List from Range Using Excel VBA

Step-3:

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.

Create List from Range Using Excel VBA

Step-4:

A new window popped out. Here input the range of data that you will create a list with. Click on Ok to continue.

Create List from Range Using Excel VBA

Now in the output window select the cell where you want to get your list.

Create List from Range Using Excel VBA

Click Ok to get your list. And our job is done.

Create List from Range Using Excel VBA

3. Create List from Range Based on a Criteria

In this section, we will create a list from a data range based on criteria.

Step-1:

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.

Create List from Range Based on a Criteria

Step-2:

In cell D4 under the Unique List column, Apply the INDEX with the MATCH formula. Insert the values and the final formula is,

=INDEX(B4:B24,MATCH(0,COUNTIF($D$3:D3,B4:B24),0))

Where,

  • 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).

Create List from Range Based on a Criteria

This formula is an array formula. So, press “CTRL+SHIFT+ENTER” to apply this formula

Create List from Range Based on a Criteria

Step-3:

Now apply the same formula to the rest of the cells to get the final result.

Create List from Range Based on a Criteria

Quick Notes

➤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.

Conclusion

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

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo