Avatar of Gilberto Sanches
Gilberto Sanches
Flag 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.
Microsoft ExcelDesktopsMicrosoft Office

Avatar of undefined
Last Comment
Gilberto Sanches

8/22/2022 - Mon
Martin Liss

What does the data look like and what should the results look like?
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
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

Or just a pivot table can create a list
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Gilberto Sanches

ASKER
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 Sanches

ASKER
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 Sanches

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
byundt

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 Sanches

ASKER
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