Excel Unique of Non-Adjacent Columns - Episode 2252

Microsoft Excel Tutorial: How to Get a Unique List of Sales Reps and Products Using the New UNIQUE Function in Excel.

Welcome back to the MrExcel netcast! In this episode, we will be discussing how to get a unique list of sales reps and products using the new unique function in Excel. After trying for quite some time, I couldn't figure it out, but thanks to a suggestion from Joe McDade, I have a solution to share with you today.

Normally, to get a unique list, we would use the advanced filter or the remove duplicates function. However, with the new unique function, we can do it in a more efficient way. In this video, I will show you how to use the unique function to get a unique list of non-adjacent columns.

First, we will go through the traditional method of using the advanced filter. Then, we will use the remove duplicates function in Excel 2010. But the real magic happens when we use the new unique function. I will show you how to use the unique function along with the CHOOSE function to get a unique list of non-adjacent columns. This method is much simpler and more efficient than the traditional methods.

In this video, I will also introduce you to the concept of lifting and broadcasting in Excel. These concepts are crucial in understanding how the new arrays work and how we can use them to our advantage. I will also show you how to sort the unique list using the SORT function. And finally, I will share with you a free resource, my book "Excel Dynamic Arrays - Straight to The Point", which is the complete guide to the new arrays in Excel. So, make sure to download it using the link in the YouTube description.

I want to thank Joe McDade for his suggestion and for helping me find a solution to this problem. And I want to thank you for watching this netcast. If you found this video helpful, please subscribe to our channel and ring the bell to get notified of our latest videos. And don't forget to download my book for free before the end of 2018. Thank you for watching and we'll see you in the next episode of the MrExcel netcast.

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

How to get a unique combination of all values in Excel columns B & D? Normally, use Advanced Filter or, Remove Duplicates. But today, how to use UNIQUE and CHOOSE to solve the problem.
Table of Contents:
(0:00) Intro to Getting a UNIQUE from two adjacent columns in Excel
(0:35) Advanced Filter for Unique Combinations
(1:19) Remove Duplicates for Unique Combinations
(2:35) Download the Dynamic Arrays book
(2:45) The concept of Lifting in Excel
(3:10) How CHOOSE normally works
(3:41) Lifting CHOOSE for two columns
(4:25) Wrapping in the UNIQUE function
(4:47) Testing Our Knowledge: Returning 3 columns
(5:43) Array constants and Broadcasting
(5:53) Sorting Results using SORT and pairwise lifting
(6:38) Testing our Knowledge: Sorting by another sequence
(7:02) Clicking Like really helps the algorithm

This video answers these common search terms:
Advanced filter
CHOOSE function
Data copy and paste
Dynamic arrays
Excel Dynamic Arrays - Straight to The Point book
Learn Excel
Remove duplicates
Sales rep and product
Sorting data
Unique from Non Adjacent Columns

Title Image Photo Credit: Photo by The Roaming Platypus on Unsplash

[excelisfun]

Awesome Video!!!! Before Joe invented the word "Lifting", that old antique book termed it "Function Argument Array Operation" for a function argument that expects a single value but we give it more than a single operation. Amazing Array Formula, Mr Excel : )

[GeertDelmulle]

WOW! That CHOOSE trick is awesome!
An effective way to dynamically ‘concatenate’ arrays.
Thank you sir.

[brookstory]

Wish i could click that Like Button a billion more times. BIll you are the "BEST"!!!!

[SergioAlejandroCampos]

Yeah! It Is a great way to use CHOOSE function.

[wayneedmondson]

Hi Mr. Excel.. dang that's cool! Thanks for the clever trick and also for offering your new Straight to the Point book for free download.. very generous and appreciated! Thumbs up!

[ennykraft]

Thank you for this! I need a drop down from two non adjacent ranges and even though I had figured out that {} had to be involved, I couldn't come up with a formula that worked. Your formula is fantastic!

But when I tried to use O2# as the reference for my data validation, I got an error since data validation wants either one column or one row. Turns out you need to first set up your formual so that it only gives you only one column, then create the data validation. If you then expand the range with your formula, data validation works.

[mathew]

Just a note, on an old solution; you could concatenate the values of interest (either with the function, or with &); once done then remove duplicates, just as you show earlier in the video

[gaetanmourmant]

This seems to work as well : =UNIQUE(FILTER(A2:F227, (A1:F1=B1)+(A1:F1=D1)+(A1:F1=E1)))

[sandip_bettereveryday]

So that means if I want to FILTER products by rep Joey, and want to show both the columns, I can use: =FILTER(CHOOSE({1, 2}, B2:B227, D2:D227), B2:B227="Joey") And if we want to show columns reordered then we can use also!

[cgrablew]

I thought this would work for a filter function but it only works putting together one column at a time ...if you try to choose({1, 2}, A1:B1000, D1:G1000) it doesn't work. What I'm trying to do is use filter to return non-contiguous columns. Any idea on how to do this?

[georgez]

How can one change in Excel lists of the same numbers but in different areas on sheet? Example 1 2 3 4 5. Now if I wanted all the 1's to change if I entered 6 and 2's to 10 and so on in place of 1, 2, 3, 4 or 5 what formula would I use rather than going through the entire page changing all of them one by one or using ctrl H?

[richardhay]

I assume (can not try it since I do not yet have theses Dynamic arrays from my insider) that you could similarly use CHOOSE to simply reorder the columns in the data set (for example CHOOSE {1, 2, etc}, D2:D227, B2:B227, etc))

[ravikoushik]

We could have also used classic pivot and selecting three columns
Then in format selected repeat data labels
In case formula gets complicated

[richardhay]

I am posting this issue here only because it relates to Dynamic Arrays and perhaps you have insight. When I us a date in the " to include" argument in FILTER (such a >=1/1/2015) I can get it to work only if I use the serial number of the date. Is this a bug, a feature or am I doing something wrong?

[patrickschardt]

In the video, you are just typing in cell O2 for the choose/unique formula. Yet, the results populate for all values of B2:B227 and D2:d227 that make the formula true. What did I miss? Also, when I type in the unique function, I get a name error