Excel: UNIQUE Function - New dynamic array function by Chris Menard

The UNIQUE function returns a list of unique values in a list or range. The UNIQUE Function is a dynamic array function. It came out in September 2018 to eOffice 365 insiders and will continue to be rolled out to all Office 365 users.

The UNIQUE function is one of seven new dynamic array functions.

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[sobysonics]

love the distinction you made between distinct and unique

[deldube]

Great Video. I was looking for a function that could find distinct values. Again, very well Hope you get more views.

[fernandogamez]

Great video! Have you tried using the UNIQUE formula when your list has blanks? Have you figured out how to exclude those?

[sandeepgill]

Hi Sir

Please let me know we can use this function in excel 2013

[mash]

Hi Chris

I am a great fan of your channel, and I must say that I get most of my issues resolved by just looking at your channel. However, I came across these 2 problems that I struggling to see how I can resolve it using the filter function

1- How can use Filter function where under the "Include" I use a spill range. So for example, I created a unique dates values from my table using the UNIQUE function. Now in my Filter function and in the include variable, I want to reference this Date Spill range. e.g.
=FILETER(My_Table, Mytable[My_dates]=A2#), where A2# is referencing this unique spill range of all the dates from My_Table. I get #N/A error

2- in My_Table where I have 3 columns: Col1, Just string values e.g. A, B, C, D; Col2, Dates values e.g. 26/01/2020, 27/01/2020; Col3 just numeric values the table looks like will be as below;

Col1 Col2 Col3

A 26/01/2020 1058

D 26/01/2020 111

A 26/01/2020 104

B 26/01/2020 83

C 26/01/2020 75

A 27/01/2020 69

C 27/01/2020 68

D 27/01/2020 60

B 27/01/2020 46

C 28/01/2020 44

C 28/01/2020 40

B 28/01/2020 36

A 28/01/2020 36


My question, how can I use Filter function or any of the dynamic arrays function to get the following

Col1 Col2 Total Col3

A 26/01/2020 1162

27/01/2020 69

28/01/2020 36

B 26/01/2020 83

27/01/2020 46

28/01/2020 36

C 26/01/2020 75

27/01/2020 68

28/01/2020 84

D 26/01/2020 111

27/01/2020 60


Basically grouping or summing the numeric values per date and per text value

I know I can do this with Pivot table or other normal functions, but reason I want dynamic arrays function is because its dynamic =and MY_Table gets updated and changes a lot and my dashboard will be automatically updated as it will be reading from these dynamic arrays...

Any help will help appreciated.

Thanks
Mash