We help IT Professionals succeed at work.

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.
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What does the data look like and what should the results look like?
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
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.
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
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?
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Or just a pivot table can create a list
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
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.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
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.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
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.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Gilberto SanchesFreelance ICT Consultant

Author

Commented:
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