Excel getting the unique values going across a row.
The Remove Duplicates command in Excel always removes rows. What if you want to remove duplicates that appear in the same row? The UNIQUE function offers a By Column option.
Also in this video: Sorting sideways in Excel using the Left-to-Right Sort option.
What happens if you leave off the optional By_Col argument in UNIQUE.
Why UNIQUE returns the original range.
Table of Contents
(0:00) Sort or Unique sideways in Excel
(0:37) UNIQUE by Column
(1:48) Nested arrays not supported
(2:00) Sorting sideways in Excel
(2:38) UNIQUE of rectangular range
(3:33) Why UNIQUE returns original range
(4:15) BYCOL defaults to False
(4:35) UNIQUE of rectangular range
(4:55) Wrap-up
This video answers these questions:
Excel automatic list of unique values?
Excel count unique values using formula?
Excel get unique values from column formula?
Excel get unique values from range?
Excel get unique values using formula?
Excel highlight unique values between two columns?
Excel how to extract unique values in a column?
Excel how to unique values?
Excel is unique formula?
Excel keep distinct?
Excel pull unique values from list?
Excel pull unique values from range?
Excel unique across multiple columns?
Excel unique across two columns?
Excel unique based on two columns?
Excel unique by column?
Excel unique by row?
Excel unique entire column?
Excel unique for multiple columns?
Excel unique function multiple columns?
Excel unique list from multiple columns?
Excel unique multiple columns?
Excel unique multiple columns into one?
Excel unique not working?
Excel unique on multiple columns?
Excel unique over multiple columns?
Excel unique sort alphabetically?
Excel unique sorted list?
Excel unique spill horizontal?
Excel unique two columns?
Excel unique values from multiple columns?
Excel unique values in two columns?
Excel unique with multiple columns?
How does unique work in Excel?
Microsoft Excel unique values in column?
Unique names in Excel column?
Using UNIQUE function in Excel?
Youtube Excel unique?
The Remove Duplicates command in Excel always removes rows. What if you want to remove duplicates that appear in the same row? The UNIQUE function offers a By Column option.
Also in this video: Sorting sideways in Excel using the Left-to-Right Sort option.
What happens if you leave off the optional By_Col argument in UNIQUE.
Why UNIQUE returns the original range.
Table of Contents
(0:00) Sort or Unique sideways in Excel
(0:37) UNIQUE by Column
(1:48) Nested arrays not supported
(2:00) Sorting sideways in Excel
(2:38) UNIQUE of rectangular range
(3:33) Why UNIQUE returns original range
(4:15) BYCOL defaults to False
(4:35) UNIQUE of rectangular range
(4:55) Wrap-up
This video answers these questions:
Excel automatic list of unique values?
Excel count unique values using formula?
Excel get unique values from column formula?
Excel get unique values from range?
Excel get unique values using formula?
Excel highlight unique values between two columns?
Excel how to extract unique values in a column?
Excel how to unique values?
Excel is unique formula?
Excel keep distinct?
Excel pull unique values from list?
Excel pull unique values from range?
Excel unique across multiple columns?
Excel unique across two columns?
Excel unique based on two columns?
Excel unique by column?
Excel unique by row?
Excel unique entire column?
Excel unique for multiple columns?
Excel unique function multiple columns?
Excel unique list from multiple columns?
Excel unique multiple columns?
Excel unique multiple columns into one?
Excel unique not working?
Excel unique on multiple columns?
Excel unique over multiple columns?
Excel unique sort alphabetically?
Excel unique sorted list?
Excel unique spill horizontal?
Excel unique two columns?
Excel unique values from multiple columns?
Excel unique values in two columns?
Excel unique with multiple columns?
How does unique work in Excel?
Microsoft Excel unique values in column?
Unique names in Excel column?
Using UNIQUE function in Excel?
Youtube Excel unique?
Transcript of the video:
Today we're going to take a look at the UNIQUE function going across each individual row.
Now look, in Excel, we've always had Remove Duplicates, and that always removes rows.
There's no way for it to go across. You can't remove columns. SORT, most people don't realize this, it usually will rearrange rows, but it can rearrange columns if you go into Options and sort left to right.
So the SORT function, the new Dynamic Array function from what, 2019, it supports left to right.
But then the UNIQUE function does something that Remove Duplicates won't do, it also supports left to right comparison.
So here we have individual rows and I just want to look across this row, dill, banana, dill, cherry, cherry, and get a UNIQUE list of products.
So remove the second dill, remove the second cherry.
And the way that we would do that, we specified the array is B3 to F3.
And then here the By_Col, if that's false or omitted, they're doing what Remove Duplicates would do, in other words, looking down the column.
But if you change this to By_Col, then Excel is looking across.
So we're able to successfully get dill, banana, cherry. Down here, elderberry, apple, banana, dill.
There should be four, elderberry, apple, banana, dill.
I'm getting rid of the duplicated dill.
And here in this row where they're all apple, we're getting just one item.
Now, it would be really nice if we didn't have to repeat this function all the way down, if we could just do a UNIQUE of the whole thing.
And in theory you should be able to put that inside of the BYROW, but that is hitting that arrays limitation, which they're now explaining as, "Nested arrays are not supported".
So that's pretty cool that we can do a UNIQUE going across.
The Remove Duplicates that's back here on the data tab, it's always looking down the column and not comparing across.
And of course, it would be nice if we could sort this into banana, cherry, dill and we can do that. The SORT function here.
It's a little mind bending because it's not doing the SORT like we would normally do.
In the SORT we specify the array is the result of the UNIQUE, we're sorting by the first row.
The SORT order is ascending, and then right here is that fourth argument where we're saying that we want to sort by column, changing that to true.
If you leave that off, which is what we do most of the time, it just defaults to false, which sorts by row.
So now we're successfully sorting that data. So it always appears alphabetically going across.
All right.
So now if we take the UNIQUE of a rectangular range and specify that By_Col is true, it's not going to do what we just did here.
It's only going to look for an instance where an entire column, so here, this orange column here is duplicated in F, and the results are removing that entire column.
It's the same way that it works with rows.
If we would leave off the By_Col and they exactly once, it's only going to look for an instance where the entire row is the same.
So here, this red row and this red row, the second one's being removed, this blue row and this blue row, this second one is being removed. So you see, we end up with two less rows.
So if you're doing UNIQUE on a rectangular range, it only removes something if the entire column in this case, or entire row in this case is the same all the way across or all the way down.
All right.
So then that brings up the question of this formula here.
So we have a column of data, and in the UNIQUE function we say, I want the UNIQUE of that range. And the By_Col is equal to true.
And the question is, well, why am I getting the exact same data that I got in the original instance? It doesn't seem like it's working.
Well, no, it is working because remember, they're looking across the row and you would need to have two things in that one row in order for anything to be removed.
Well, since there's only one thing, the UNIQUE of one item is the one item.
So I think this is doing exactly what it's supposed to do.
And then I read somewhere that they said, if you omit the second argument, that it defaults to true. Well, Excel Help says that it defaults to false.
And even just doing it, you see that if you leave that second argument off, it is removing things on a row-by-row basis.
Now, just to point out here, if you ended up at this video. And you want the UNIQUE list of products that appear in any individual cell.
Then we use the new TOCOL function to convert this into a vector, and then the UNIQUE and then the SORT, and we get a list of products that appear anywhere in that rectangular range.
It's somewhat interesting that the UNIQUE function is doing something that's not available in the Excel user interface, so I can see why that might be a little bit confusing.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Now look, in Excel, we've always had Remove Duplicates, and that always removes rows.
There's no way for it to go across. You can't remove columns. SORT, most people don't realize this, it usually will rearrange rows, but it can rearrange columns if you go into Options and sort left to right.
So the SORT function, the new Dynamic Array function from what, 2019, it supports left to right.
But then the UNIQUE function does something that Remove Duplicates won't do, it also supports left to right comparison.
So here we have individual rows and I just want to look across this row, dill, banana, dill, cherry, cherry, and get a UNIQUE list of products.
So remove the second dill, remove the second cherry.
And the way that we would do that, we specified the array is B3 to F3.
And then here the By_Col, if that's false or omitted, they're doing what Remove Duplicates would do, in other words, looking down the column.
But if you change this to By_Col, then Excel is looking across.
So we're able to successfully get dill, banana, cherry. Down here, elderberry, apple, banana, dill.
There should be four, elderberry, apple, banana, dill.
I'm getting rid of the duplicated dill.
And here in this row where they're all apple, we're getting just one item.
Now, it would be really nice if we didn't have to repeat this function all the way down, if we could just do a UNIQUE of the whole thing.
And in theory you should be able to put that inside of the BYROW, but that is hitting that arrays limitation, which they're now explaining as, "Nested arrays are not supported".
So that's pretty cool that we can do a UNIQUE going across.
The Remove Duplicates that's back here on the data tab, it's always looking down the column and not comparing across.
And of course, it would be nice if we could sort this into banana, cherry, dill and we can do that. The SORT function here.
It's a little mind bending because it's not doing the SORT like we would normally do.
In the SORT we specify the array is the result of the UNIQUE, we're sorting by the first row.
The SORT order is ascending, and then right here is that fourth argument where we're saying that we want to sort by column, changing that to true.
If you leave that off, which is what we do most of the time, it just defaults to false, which sorts by row.
So now we're successfully sorting that data. So it always appears alphabetically going across.
All right.
So now if we take the UNIQUE of a rectangular range and specify that By_Col is true, it's not going to do what we just did here.
It's only going to look for an instance where an entire column, so here, this orange column here is duplicated in F, and the results are removing that entire column.
It's the same way that it works with rows.
If we would leave off the By_Col and they exactly once, it's only going to look for an instance where the entire row is the same.
So here, this red row and this red row, the second one's being removed, this blue row and this blue row, this second one is being removed. So you see, we end up with two less rows.
So if you're doing UNIQUE on a rectangular range, it only removes something if the entire column in this case, or entire row in this case is the same all the way across or all the way down.
All right.
So then that brings up the question of this formula here.
So we have a column of data, and in the UNIQUE function we say, I want the UNIQUE of that range. And the By_Col is equal to true.
And the question is, well, why am I getting the exact same data that I got in the original instance? It doesn't seem like it's working.
Well, no, it is working because remember, they're looking across the row and you would need to have two things in that one row in order for anything to be removed.
Well, since there's only one thing, the UNIQUE of one item is the one item.
So I think this is doing exactly what it's supposed to do.
And then I read somewhere that they said, if you omit the second argument, that it defaults to true. Well, Excel Help says that it defaults to false.
And even just doing it, you see that if you leave that second argument off, it is removing things on a row-by-row basis.
Now, just to point out here, if you ended up at this video. And you want the UNIQUE list of products that appear in any individual cell.
Then we use the new TOCOL function to convert this into a vector, and then the UNIQUE and then the SORT, and we get a list of products that appear anywhere in that rectangular range.
It's somewhat interesting that the UNIQUE function is doing something that's not available in the Excel user interface, so I can see why that might be a little bit confusing.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
