How to create a sorted unique list in an Excel spreadsheet

3 years ago 399

Microsoft 365's UNIQUE() relation is simply a large crushed to upgrade. Combine UNIQUE() and SORT() to make unsocial lists with a azygous formula.

Pointing astatine spreadsheet connected laptop

Lists are a cardinal portion successful astir immoderate Microsoft Excel application, and fortunately, they're casual to generate. There are 2 kinds of lists you mightiness encounter: a static database and a database based connected earthy data. Without moving a existent poll, I fishy that the second is the astir common.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

This benignant of database allows you to make dropdown lists and truthful connected to assistance information introduction and support the validity of your data. For instance, you mightiness privation to make a dropdown database that allows a idiosyncratic to take worker oregon pupil names alternatively than typing them manually. This benignant of database is based connected a information acceptable that really stores those values. In this article, I'll amusement you 2 ways to make a unsocial database of values based connected a azygous file successful a information set:

An precocious filter
UNIQUE()

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system. UNIQUE() is disposable lone successful Microsoft 365, Excel for the Web, and Excel for Android tablets and phones. For your convenience, you tin download the .xlsx objection file.

Pre-Microsoft 365: How to usage the Advanced Filter successful Excel

Once Excel introduced the AutoFilter feature, Excel's archetypal filtering features were often ignored. But occasionally, you'll tally into a concern that the AutoFilter can't fulfill. When this happens, you tin crook to Excel's Advanced Filter feature, which supports much precocious options. This is wherever you crook erstwhile you request a unsocial database based connected existing data. But earlier we get started, let's reappraisal a fewer differences betwixt the 2 filtering features:

AutoFilter works with a static information set. Using the precocious feature, you tin transcript information to different location. You tin region duplicates from the root information acceptable but doing truthful really alters the information set.
The precocious diagnostic supports analyzable criteria.
The precocious diagnostic lets you extract a unsocial database oregon unsocial records—that's what we'll beryllium doing.

Before UNIQUE() was available, you mightiness person utilized an precocious filter to make a unsocial database of values. We'll reappraisal this diagnostic for those of you not utilizing Microsoft 365. It's important to retrieve that this method isn't dynamic, adjacent if you usage a Table entity to store your data.

Figure A displays a elemental information acceptable with duplicate values successful each column. Now, let's accidental you request a database of unsocial values successful the Personnel column.

Figure A

To make that database manually, bash the following:

Click immoderate compartment successful the information set.
Click the Data tab and past click Advanced successful the Sort & Filter group.
Click the Copy to Another Location option.
Excel volition show the compartment notation for the full information acceptable oregon Table entity arsenic the List Range. If you clasp this selection, Excel volition instrumentality a unsocial information acceptable based connected each the columns. Instead, prime the Personnel file by clicking the power and past selecting D2:D13. If you're utilizing a Table object, this diagnostic mightiness amusement the file sanction alternatively of the compartment range.
Remove the Criteria Range if determination is one.
Click the Copy To power and past prime an out-of-the-way cell, specified arsenic H2. Be definite to specify a scope that's ample capable truthful you don't accidentally constitute implicit existing data.
Check the Unique Records Only option, arsenic shown successful Figure A.
Click OK.

Figure B shows the unsocial list. There are six unsocial names successful the Personnel column. The database is abstracted from your root data. In addition, the database isn't dynamic. If you participate a grounds for a caller employee, you indispensable retrieve to update the database manually. If you privation the database sorted, prime the list, minus the header substance and take a benignant bid successful the Sort & Filter group. Now let's determination connected to the 2 functions that region each of the manual steps and generates a dynamic list: UNIQUE() and SORT().

Figure B

How to usage the UNIQUE() relation successful Excel

If you're utilizing Microsoft 365 oregon 1 of the 2019 standalone versions of Excel, you tin rapidly make a dynamic database utilizing the UNIQUE() function. This relation returns a database of unsocial values successful a database oregon range, utilizing the pursuing syntax:

UNIQUE(array, [by_col], [exactly_once])

The array statement is the scope you privation to trim to a unsocial list. The by_col statement is simply a Boolean value: TRUE compares columns and returns unsocial columns; FALSE is the default and volition comparison rows against rows and returns unsocial rows. The exactly_once statement is besides a Boolean value: TRUE returns each chiseled rows oregon columns that hap precisely erstwhile from the scope oregon array; FALSE, the default, returns each chiseled rows oregon columns from the range.

SEE: Windows 10: Lists of vocal commands for code designation and dictation (free PDF) (TechRepublic)

For our intent of returning a unsocial database from a azygous column, we lone request the array argument. Select a compartment (I'm utilizing H2 again) and participate the pursuing relation by selecting and not typing cells D2:D13, if you're utilizing a Table object—let Word fig retired the nonstop syntax. If you're utilizing a regular range, benignant the scope code if you like. Figure C shows the results, which uses a spill range. When utilizing UNIQUE(), you tin show the header cell, oregon omit it by eliminating it from the selection.

Figure C

Microsoft 365 present supports what's known arsenic a spill range, which is the effect of a dynamic array look that returns aggregate values—its output spills beyond the input cell. In short, a spill scope is simply a scope of calculated results from 1 formula. When you prime immoderate compartment successful a spill range, Excel highlights the full scope with a bluish borderline and the relation successful the look bar. You volition ever find the look successful the topmost cell.

Unlike the manual database we created earlier utilizing an precocious filter, the results of UNIQUE() are dynamic. As you tin spot successful Figure D, I added a caller worth to the Personnel column, and the database successful file H updates automatically.

Figure D

How to usage SORT() wrong the results of UNIQUE() successful Excel

Unlike the resulting database generated by the precocious filter, you tin usage the SORT() relation to benignant the results of UNIQUE() and it couldn't beryllium easier. Figure E shows the effect of wrapping UNIQUE() successful SORT(). As you tin see, the results are sorted, but tin you find the error? The look sorts Personnel with the remainder of the values and you won't privation that to happen. The elemental cure is to region the header compartment from the array scope arsenic follows:

=SORT(UNIQUE(D3:D13))

Figure E

If you're inactive utilizing an earlier version, usage the Advanced Filter diagnostic to make a static, unsorted database of unsocial values based connected earthy data. If you're connected Microsoft 365, wrapper a UNIQUE() relation successful a SORT() relation for a dynamic and sorted list.