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.
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
Last Comment
Gilberto Sanches
What does the data look like and what should the results look like?
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.
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
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.
Or just a pivot table can create a list
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.
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.
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.
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.
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.
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))),"")
ASKER
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