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:36) 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) Wrap-Up
To download this workbook: https://www.mrexcel.com/download-center/2018/11/unique-from-non-adjacent-columns.xlsx
List of upcoming seminars: Excel Seminar Schedule
For information about the Excel Dynamic Arrays book: Excel Dynamic Arrays Straight to the Point
Title Image Photo Credit: Photo by The Roaming Platypus on Unsplash
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:36) 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) Wrap-Up
To download this workbook: https://www.mrexcel.com/download-center/2018/11/unique-from-non-adjacent-columns.xlsx
List of upcoming seminars: Excel Seminar Schedule
For information about the Excel Dynamic Arrays book: Excel Dynamic Arrays Straight to the Point
Title Image Photo Credit: Photo by The Roaming Platypus on Unsplash
Transcript of the video:
Learn Excel from MrExcel podcast episode 2252 - Unique from Non Adjacent Columns Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. The other day I was trying to figure out if there was a way that I could get a unique list of sales rep and product using the new unique function and after trying for quite some time couldn't figure it out. All right but after talking to Joe McDade, Joe gave me an idea and I do have a way to do this today. Now, first let's talk about how we would normally do this. All right, so it's sales rep and product and whatever unique combination of those two. Normally I would come out to data and advanced filter and a little pre-work here, you have to take the two headings that you want, in this case rep and product, and copy them out to a new output range.
All right, so we go back to our original data, click advanced.
It says the data is in A1 to D227. That's great.
We're going to copy it in another location.
The location we're gonna copy to, has those two headings and we ask for unique records only and it returns every unique combination of sales rep and product, which is beautiful.
Now, it doesn't sort it, but you know that that's just the way it goes.
And that's the way that I would have done this, you know, every day up until Excel 2010 came out.
Once Excel 2010 came out, then we can copy that data out to a new spot here, and then Data > Remove Duplicates.
You always want to make a copy of your data because Remove Duplicates is destructive and I only want to base it on sales rep and product.
Click OK. All right same answers that we had back there with the extra column.
I'll just right-click and delete and you'll have your results.
All right.
But is there some way that we could get a unique list of just B and D using the new unique function?
All right, so you know unique, normally you would specify your data, kind of like this, but unfortunately that would give us every unique combination of rep, date and product and so is there some way that we can say, "Hey, look we just want B2 to B227 and then also D2 to D 227, but I couldn't figure out any good way to do that.
Now, when dynamic arrays came out, I said, it was going to be simpler, we wouldn't have to worry about control+shift+enter in all the old concepts for dynamic arrays, but it turns out that in this particular case, we do have to turn back to the old arrays, and hopefully by now you've downloaded my book Excel Dynamic Arrays - Straight to The Point. It's free through the end of the year and go back to chapter 9. Pages 31 and 32. It talks about the concept of lifting. Lifting is when if Excel function expects a single value but yet we pass it several values, even back in the old Excel, it would do all of the calculations. The problem was it wouldn't be able to give us all of the answers, but now thanks to the ability for Excel to spill answers into multiple cells, it's going to work. All right, so I set up a quick little formula there of RAND between 1 and 2 and you probably are familiar with the CHOOSE function. CHOOSE, we send it a single number and then we say if it's 1, we want apple; if it's 2 we want banana. And depending on whether this is 1 or 2, I'll calculate a couple of times, you see, that when the number R1 is 1, we get Apple, when the number in R1 is 2, we get banana. Simple enough. Right? Here's what I'm going to do. Instead of passing CHOOSE a single function, I'm going to pass it an array of numbers. Someone asked for 1,2 as an array constant and that is going to cause CHOOSE to lift and to do the CHOOSE function twice.
So for the iteration where CHOOSE is looking at 1, call and return all the sales rep names, comma, and for the iteration where CHOOSE is looking at two, I want to return all of the product names. So there's our formula, I press ENTER and it's going to give me a copy of everything that's over in column B and D, all the way down. Al right? That is just beautiful, because now I'm returning a single array that has those two values, and from there it is super easy to get the answer. Here's the UNIQUE like that, press enter and I get just the unique combination. Still, not sorted not sorted like advanced filter would do, not sorted like remove duplicates would do. If you like what you see in this video please subscribe and ring that Bell.
All right, now let's just test our understanding here. If I wanted to do a unique combination of three columns rep, product and color, I would have to come here to the array that's asking for 1, 2 and I add a comma 3 and then out here in the CHOOSE functions, this is the second argument to choose, the third argument to choose, I would ask for E2:E227 like that. All right? And of course, if you needed a fourth one just add a number 4 and then whichever column you want and then we press ENTER.
And now a larger array of results because apple is available in both red, green, oh, and gold. All right so the answers expand. All right, so we're using something called lifting here, even though we're using the new arrays, we're still able to lift that. And where CHOOSE was expecting a single answer, we're passing it three answers, which forces CHOOSE to return three sets of results because these are separated by commas, each result goes in a new column. In essence using a term called broadcasting. All right, now from here, it would be be really nice to be able to sort this.
So we just say SORT of the whole thing and then SORT wants to know which index we want. Well, I actually want to sort by all three columns. So again, I'm gonna do lifting but this will be paralyzed lifting. So I'm gonna say SORT by columns 1, 2 and 3, and then when it says what's the order, well I'm just going to go ascending, ascending, ascending and in this case because we're passing it to arrays that are the same shape, it's going to match up the 1 and the 1 the 2 and the 1 and the 3 and the 1 and should correctly sort this data, first by sales rep, within sales rep by product, within product by color and that all works. Let's just see here if we can kind of finesse this a little bit. Let's sort by column 3 first and then by column 1 and then by column 2 and we'll make one of these be descending like that, and so yeah we're alphabetical by gold within the green. It's going descending, so it's not that you have to go in column 1, column 2, column 3 sequence. Now, hey look this book Excel Dynamic Arrays Straight to The Point is the complete guide to these new arrays and you want to go download this. Use the link down there in the the YouTube description.
This book is free at least through the end of 2018.
Probably free until dynamic arrays reach general availability, which would be sometime in 2019, so go download it sooner rather than later.
Well, hey, I want to thank Joe McDade for pointing me in a direction there, where we could use lifting, broadcasting and pairwise lifting to solve this problem. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen. The other day I was trying to figure out if there was a way that I could get a unique list of sales rep and product using the new unique function and after trying for quite some time couldn't figure it out. All right but after talking to Joe McDade, Joe gave me an idea and I do have a way to do this today. Now, first let's talk about how we would normally do this. All right, so it's sales rep and product and whatever unique combination of those two. Normally I would come out to data and advanced filter and a little pre-work here, you have to take the two headings that you want, in this case rep and product, and copy them out to a new output range.
All right, so we go back to our original data, click advanced.
It says the data is in A1 to D227. That's great.
We're going to copy it in another location.
The location we're gonna copy to, has those two headings and we ask for unique records only and it returns every unique combination of sales rep and product, which is beautiful.
Now, it doesn't sort it, but you know that that's just the way it goes.
And that's the way that I would have done this, you know, every day up until Excel 2010 came out.
Once Excel 2010 came out, then we can copy that data out to a new spot here, and then Data > Remove Duplicates.
You always want to make a copy of your data because Remove Duplicates is destructive and I only want to base it on sales rep and product.
Click OK. All right same answers that we had back there with the extra column.
I'll just right-click and delete and you'll have your results.
All right.
But is there some way that we could get a unique list of just B and D using the new unique function?
All right, so you know unique, normally you would specify your data, kind of like this, but unfortunately that would give us every unique combination of rep, date and product and so is there some way that we can say, "Hey, look we just want B2 to B227 and then also D2 to D 227, but I couldn't figure out any good way to do that.
Now, when dynamic arrays came out, I said, it was going to be simpler, we wouldn't have to worry about control+shift+enter in all the old concepts for dynamic arrays, but it turns out that in this particular case, we do have to turn back to the old arrays, and hopefully by now you've downloaded my book Excel Dynamic Arrays - Straight to The Point. It's free through the end of the year and go back to chapter 9. Pages 31 and 32. It talks about the concept of lifting. Lifting is when if Excel function expects a single value but yet we pass it several values, even back in the old Excel, it would do all of the calculations. The problem was it wouldn't be able to give us all of the answers, but now thanks to the ability for Excel to spill answers into multiple cells, it's going to work. All right, so I set up a quick little formula there of RAND between 1 and 2 and you probably are familiar with the CHOOSE function. CHOOSE, we send it a single number and then we say if it's 1, we want apple; if it's 2 we want banana. And depending on whether this is 1 or 2, I'll calculate a couple of times, you see, that when the number R1 is 1, we get Apple, when the number in R1 is 2, we get banana. Simple enough. Right? Here's what I'm going to do. Instead of passing CHOOSE a single function, I'm going to pass it an array of numbers. Someone asked for 1,2 as an array constant and that is going to cause CHOOSE to lift and to do the CHOOSE function twice.
So for the iteration where CHOOSE is looking at 1, call and return all the sales rep names, comma, and for the iteration where CHOOSE is looking at two, I want to return all of the product names. So there's our formula, I press ENTER and it's going to give me a copy of everything that's over in column B and D, all the way down. Al right? That is just beautiful, because now I'm returning a single array that has those two values, and from there it is super easy to get the answer. Here's the UNIQUE like that, press enter and I get just the unique combination. Still, not sorted not sorted like advanced filter would do, not sorted like remove duplicates would do. If you like what you see in this video please subscribe and ring that Bell.
All right, now let's just test our understanding here. If I wanted to do a unique combination of three columns rep, product and color, I would have to come here to the array that's asking for 1, 2 and I add a comma 3 and then out here in the CHOOSE functions, this is the second argument to choose, the third argument to choose, I would ask for E2:E227 like that. All right? And of course, if you needed a fourth one just add a number 4 and then whichever column you want and then we press ENTER.
And now a larger array of results because apple is available in both red, green, oh, and gold. All right so the answers expand. All right, so we're using something called lifting here, even though we're using the new arrays, we're still able to lift that. And where CHOOSE was expecting a single answer, we're passing it three answers, which forces CHOOSE to return three sets of results because these are separated by commas, each result goes in a new column. In essence using a term called broadcasting. All right, now from here, it would be be really nice to be able to sort this.
So we just say SORT of the whole thing and then SORT wants to know which index we want. Well, I actually want to sort by all three columns. So again, I'm gonna do lifting but this will be paralyzed lifting. So I'm gonna say SORT by columns 1, 2 and 3, and then when it says what's the order, well I'm just going to go ascending, ascending, ascending and in this case because we're passing it to arrays that are the same shape, it's going to match up the 1 and the 1 the 2 and the 1 and the 3 and the 1 and should correctly sort this data, first by sales rep, within sales rep by product, within product by color and that all works. Let's just see here if we can kind of finesse this a little bit. Let's sort by column 3 first and then by column 1 and then by column 2 and we'll make one of these be descending like that, and so yeah we're alphabetical by gold within the green. It's going descending, so it's not that you have to go in column 1, column 2, column 3 sequence. Now, hey look this book Excel Dynamic Arrays Straight to The Point is the complete guide to these new arrays and you want to go download this. Use the link down there in the the YouTube description.
This book is free at least through the end of 2018.
Probably free until dynamic arrays reach general availability, which would be sometime in 2019, so go download it sooner rather than later.
Well, hey, I want to thank Joe McDade for pointing me in a direction there, where we could use lifting, broadcasting and pairwise lifting to solve this problem. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
