- Windows 10
- Microsoft Office
- Networking
- Microsoft Excel
- Desktops
Formula List unique items in Excel 2016
Hello exports,
Currently, I use Excel Power Query to list unique items from a list.
It works fine in Desktop version of Excel 2016. However, in the free online version the Power Query can't be refreshed.
What other solutions are there for this? (maybe a formula that works in Excel 2016.
Currently, I use Excel Power Query to list unique items from a list.
It works fine in Desktop version of Excel 2016. However, in the free online version the Power Query can't be refreshed.
What other solutions are there for this? (maybe a formula that works in Excel 2016.
The data looks like:
1. In Sheet A, a column with a lot of values (codes)
2. In sheet B, results are a list of unique values
In Power Query I use the "group by" feature for this.
1. In Sheet A, a column with a lot of values (codes)
2. In sheet B, results are a list of unique values
In Power Query I use the "group by" feature for this.
Excel Online has the UNIQUE function for this purpose. So too does Excel 2016/Office 365 on monthly update channel. Is that the version you use?
You are right Byundt. That Function works in the Excel Online & Office 265 on monthly update channel.
https://support.office.com/en-us/article/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e
In Excel 2016 Desktop version that function, and multiple others like FILTER aren't working.
https://support.office.com/en-us/article/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e
In Excel 2016 Desktop version that function, and multiple others like FILTER aren't working.
oH my goodness, you guys are such a help. Thank you very much.
The pivot table solution works.
And the new functions like Unique, Filter work in Excel Online.
However, the filter function only works online.
The unique function does work offline when first created in Excel online.
The pivot table solution works.
And the new functions like Unique, Filter work in Excel Online.
However, the filter function only works online.
The unique function does work offline when first created in Excel online.
I just discovered that the new functions, like Unique & Filter, only work online. When changes are made in the Desktop version, the new functions break. But when I go online the function works again.
If UNIQUE is working in your desktop Excel, so too should FILTER. Since you say FILTER is not, I suspect that you are either running Office 365 on the semi-annual update channel or have a perpetual license version of Office. The perpetual license version of Excel 2016 and 2019 will never get new features like UNIQUE and FILTER, but Office 365 semi-annual update channel will get them this Summer.
I recommend that power users switch to Office 365 because Microsoft has been adding useful features like UNIQUE, realtime stock prices, and super-fast lookup formulas at a much faster pace than what we were accustomed to with Excel 97 through 2016.
If you create a list of unique items using Excel Online and the UNIQUE function, and later open the file in perpetual license Excel 2016, the values will remain until you try to update the formula (such as by changing a value in its data source). At that point, you should see a #NAME? error value.
There are two ways of producing a list of unique values using formulas in older Excel versions. In addition, you can use Power Query and Advanced Filter.
The faster formula method uses a user-defined function (VBA code) and a Dictionary object. Dictionary objects only accept unique values, so the code tries to add every item from column A into the Dictionary object. The values that are successfully added are your unique values, and may be returned to the worksheet in an array formula. Since VBA isn't supported by Excel Online, I won't offer code for this purpose.
The other formula method uses an array formula in cell D2 like shown below. This formula gets slow if you make the number of rows included too large.
I recommend that power users switch to Office 365 because Microsoft has been adding useful features like UNIQUE, realtime stock prices, and super-fast lookup formulas at a much faster pace than what we were accustomed to with Excel 97 through 2016.
If you create a list of unique items using Excel Online and the UNIQUE function, and later open the file in perpetual license Excel 2016, the values will remain until you try to update the formula (such as by changing a value in its data source). At that point, you should see a #NAME? error value.
There are two ways of producing a list of unique values using formulas in older Excel versions. In addition, you can use Power Query and Advanced Filter.
The faster formula method uses a user-defined function (VBA code) and a Dictionary object. Dictionary objects only accept unique values, so the code tries to add every item from column A into the Dictionary object. The values that are successfully added are your unique values, and may be returned to the worksheet in an array formula. Since VBA isn't supported by Excel Online, I won't offer code for this purpose.
The other formula method uses an array formula in cell D2 like shown below. This formula gets slow if you make the number of rows included too large.
=IFERROR(INDEX(Sheet1!A$2:A$1000,AGGREGATE(15,6,(ROW(Sheet1!A$2:A$1000)-ROW(Sheet1!A$2)+1)/((ROW(Sheet1!A$2:A$1000)-ROW(Sheet1!A$2)+1)=MATCH(Sheet1!A$2:A$1000,Sheet1!A$2:A$1000,0)),ROWS(D$2:D2))),"")
Byundt, you are right on this
Thankss
If you create a list of unique items using Excel Online and the UNIQUE function, and later open the file in perpetual license Excel 2016, the values will remain until you try to update the formula (such as by changing a value in its data source). At that point, you should see a #NAME? error value
Thankss
Explore More ContentExplore courses, solutions, and other research materials related to this topic.
-
![]()
received a Solution
Excel - cannot save/create in network drive. -
![]()
wrote an Article
![]()
MAC addresses in Access and Excel
-
![]()
created a Video
![]()
Exchange 2016 - Setting up an email account using Office 2019
-
Explore Cloud Class® ![]()
Microsoft Office Excel 2019: Part 01
Premium Content
You need an Expert Office subscription to comment.Start Free Trial