Link to home
Create AccountLog in
Avatar of Gilberto Sanches
Gilberto SanchesFlag for Suriname

asked on

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.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

What does the data look like and what should the results look like?
Avatar of Gilberto Sanches

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Or just a pivot table can create a list
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.
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.
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.
=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))),"")

Open in new window

Byundt, you are right on this

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