Extremely tricky problem today: How can you use UNIQUE to get all unique individual cells from A1:D12? The solutions are all pretty complex. Do you have something better?
Vote for a SPLIT function at
excel.uservoice.com
Vote for changing UNIQUE at Allow UNIQUE() to operate on a rectangular range, returning all unique values
Follow Brad Yundt on Quora: Brad Yundt - Quora
Information on Fast Excel V4 from Charles Williams: FastExcel V4 SpeedTools
Cool stuff in this video:
Ingeborg Hawighorst using TEXTJOIN to turn a rectangular range into a single cell.
Fairwinds splitting that cell into a row of cells
Charles Williams add-in with SLICES, VSTACK, and UNPIVOT
Brad Yundt with a 3-line SPLITTER UDF for the win.
Vote for a SPLIT function at
UserVoice Pages
Follow Brad Yundt on Quora: Brad Yundt - Quora
Information on Fast Excel V4 from Charles Williams: FastExcel V4 SpeedTools
Cool stuff in this video:
Ingeborg Hawighorst using TEXTJOIN to turn a rectangular range into a single cell.
Fairwinds splitting that cell into a row of cells
Charles Williams add-in with SLICES, VSTACK, and UNPIVOT
Brad Yundt with a 3-line SPLITTER UDF for the win.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2317. Unique on a Rectangular Range.
Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen.
UNIQUE is one of the great new functions in Excel, but we ran into a problem where UNIQUE just doesn't do what we're looking for and I'm trying to get the UNIQUE of all of these values here, and when I enter UNIQUE what it's doing is giving me unique rows.
So I'm getting exactly the original data that I wanted when I really want this: Cherry, Dill, Fig, Iceberg, Kiwi, and Orange.
You know the only way that this is going to reduce our values is if we had three rows or two or three or five rows that were exactly the same. See then those rows get taken out?
Not at all what I want.
In order for this to work, I first have to unwind the data into a single column and that's the problem.
This was copy paste, copy, paste, copy, paste, copy, paste or yes formula formula formula formula.
But if this grows, That's not going to work.
So here's my solution to this, and my solution is going to build on my friend Ingeborg, who's an Excel MVP and Ingeborg very smartly realized that the TEXTJOIN function for that range will unwind the range into a single row of data like that.
And then I'm going to jump into a formula that I've known since 2006 to separate that data at the comma into individual words.
It got better now because I can just say the SEQUENCE from 1 to 48 and it all works alright.
Once I have that formula that I use the UNIQUE with comma True is the 2nd argument - this says do it by row instead of by column.
Transpose, then sort it and there's my answer.
The problem is this formula. Here I've been using this formula since 2006.
It is 2020 now - that's 14 years and until today I could never explain how the heck it worked and today I had to figure out how it worked because it stopped working.
All right, the first thing we do is we substitute every comma with 99 spaces and then the formula says for the first one we want 1 * 99 - 1, for 99.
And so it says basically the MID Of Of that long cell starting at 1 going for 99 and you see what happens is the word keeps moving over and then we send that into the TRIM function?
So there's the trim now. Hey, shout out here.
I first found this formula back in 2006 at Fairwinds at the MrExcel message board.
It is one of those formulas that make you say "whoa" and I tucked it away.
I could never understand it until today because you see it breaks right here.
It was all great until we get to Iceberg and then it splits iceberg into two.
So I realized.
That we would have to adjust that 99 trick and finally 299 299 298 299 Now this is a long complicated solution and it all can get reduced down to one formula the whole thing.
The problem that we have is that 299 is going to break when we get to 72 items and so we go to 399. Then there's a limit.
You can't have more than 32,767 characters in the result of a formula.
So somewhere between 72 and 90 items, this whole thing is going to fall apart.
This is another user voice idea.
If they would add a SPLIT formula, we wouldn't have to use these rogue methods.
Check down there in the YouTube description for this link and give us a vote.
So back to the drawing board I kept saying is there some way that I could on the fly get these things to unwind into a stack - a vertical stack of four columns and then I can send that into UNIQUE.
But everything I tried - well obviously that's not going to work because unique is expecting everything to be in the first argument.
So then I tried the concatenation and that didn't do it at all, and then this awesome thing here with CHOOSE(1 2 3 4 and then trying to choose the right ones.
But it's not concatenating them together, and this is actually the point right here.
I hope that one of my incredibly smart viewers who always down in the YouTube comments, have a better way to do it right here.
I'm hoping that someone will jump in and give me an awesome, awesome solution of how to take these four items and stack them together easily with the formula. Yeah, go.
All right now.
Last Monday I talked about this great new add-in from Charles Williams Version 4 Fast Excel Speed Tools and I'm going to admit I am a Fast Excel Fanboy.
I love these new functions.
I wish the Joe McDaid would give us these functions in Excel. I have these tools and I said I wonder if I can do this?
The SORT of the UNIQUE of VSTACK and then SLICES, SLICES, SLICES, SLICES.
I haven't shown these on the podcast before. Let me just take 30 seconds here.
SLICES is a great little function that says take that array A1 to D12.
I want the entire row.
I'm sorry I want the entire column so that zero says it's not a particular row - I want all of the rows and then cell O4 is 1 and then 2 and then 3 and then 4.
So SLICES will take a slice either vertical, horizontal from an array.
beautiful and then this is exactly was looking for on the last worksheet. VSTACK - vertical stack.
Take those four and stack them vertically.
From there it's a walk in the park, UNIQUE, SORT, and we're done.
So if you have Fast Excel or if Joe McDaid ever gives us this, Then it'll work, but I don't like that.
I have to say *this* comma, *this*, comma, *this* comma.
I'd really love for VSTACK to be able to allow me to say I want 1 2 3 4.
Doesn't work, gives an answer, not the answer I wanted. Or: another Fast Excel trick.
Here is a function called Unpivot. Unpivot, you know we can do unpivot in power query.
Well, Charles wrote an unpivot that just works in Excel.
The hassle is I'm completely cheating.
Because for the unpivot to work I had to point to a row of headers that aren't there.
And a row of column labels that aren't there, so I had to insert this extra row and column for this to work at all.
Once I do the Unpivot.
The third column that I want so the SLICES(,,3 And then UNIQUE and then SORT and that works alright, so this whole conversation was happening between several Excel MVP's and when I showed this one to Charles he's like, well, OK, clever.
(Thanks Charles!) but I actually have a function in Fast Excel version 3 that will do this called LISTDISTINCTS.
And here in the 4th argument By Rows, if you specify False, it does it by individual cells and items and gives us exactly what we want. Kudos to Charles Williams.
Clearly the shortest formula.
If you own Fast Excel or if Joe McDaid ever gives us this now, in the opposite in the spectrum, and Liam - Liam Bastic is my favorite funniest Excel MVP.
He was actually just being a Joker when he sent in this horrible formula.
It's like I don't know why you all think this is so hard. Just type this.
And then you're going to have a headache.
See what that does it as well.
Never going to work.
Well no it is going to work, but no one's ever going to remember it and I can't tell you how it works because I'm not going to take the time to figure out how it works.
Alright, Brad Yundt, if you follow Quora, the Excel questions in Quora - Brad is always answering Excel questions out there.
Every once in awhile I answer an Excel question on Quora and 10% of the time Brad comes along and says well with all due respect to my friend Bill Jelen.
Here is a way that is 10,000 times shorter than his way. Well just last week.
So Brad says, hey, you know, if we just had a split function that could take Ingeborg's Comma-delimited TEXTJOIN and break it apart.
That would solve it and he said you could write your own UDF - user defined function in VBA. 3 lines, 3 lines of code. How cool is that?
And then use the Splitter of the TEXTJOIN.
Send that into TRANSPOSE because of course TEXTJOIN is going across and then UNIQUE and that solution works as well.
While I love all these solutions and I love all of my Excel MVP friends, it shouldn't be this hard right?
So I went out to excel.uservoice.com. Yesterday an wrote up a new idea.
Surprised no one has asked for this, "Allow UNIQUE to operate on a rectangular range", returning all unique values I just created as one vote.
Please click down in the YouTube description this link right here. Go give me a vote.
It would be a nice thing to be able to do so, unless of course someone comes through with something easier.
Now usually I ask you to buy this book as the solutions in this book, but the solution is not in this book.
This question just came up, but the people that came up with this solution.
Yeah, they are in this book, so go check out the book Excel Insights - A Guide to the Best Parts of Excel by 24 Excel MVP's.
Click that "I" in the top right hand corner and hey if you enjoy videos like this trying to solve these weird Excel problems please subscribe and ring that Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen.
UNIQUE is one of the great new functions in Excel, but we ran into a problem where UNIQUE just doesn't do what we're looking for and I'm trying to get the UNIQUE of all of these values here, and when I enter UNIQUE what it's doing is giving me unique rows.
So I'm getting exactly the original data that I wanted when I really want this: Cherry, Dill, Fig, Iceberg, Kiwi, and Orange.
You know the only way that this is going to reduce our values is if we had three rows or two or three or five rows that were exactly the same. See then those rows get taken out?
Not at all what I want.
In order for this to work, I first have to unwind the data into a single column and that's the problem.
This was copy paste, copy, paste, copy, paste, copy, paste or yes formula formula formula formula.
But if this grows, That's not going to work.
So here's my solution to this, and my solution is going to build on my friend Ingeborg, who's an Excel MVP and Ingeborg very smartly realized that the TEXTJOIN function for that range will unwind the range into a single row of data like that.
And then I'm going to jump into a formula that I've known since 2006 to separate that data at the comma into individual words.
It got better now because I can just say the SEQUENCE from 1 to 48 and it all works alright.
Once I have that formula that I use the UNIQUE with comma True is the 2nd argument - this says do it by row instead of by column.
Transpose, then sort it and there's my answer.
The problem is this formula. Here I've been using this formula since 2006.
It is 2020 now - that's 14 years and until today I could never explain how the heck it worked and today I had to figure out how it worked because it stopped working.
All right, the first thing we do is we substitute every comma with 99 spaces and then the formula says for the first one we want 1 * 99 - 1, for 99.
And so it says basically the MID Of Of that long cell starting at 1 going for 99 and you see what happens is the word keeps moving over and then we send that into the TRIM function?
So there's the trim now. Hey, shout out here.
I first found this formula back in 2006 at Fairwinds at the MrExcel message board.
It is one of those formulas that make you say "whoa" and I tucked it away.
I could never understand it until today because you see it breaks right here.
It was all great until we get to Iceberg and then it splits iceberg into two.
So I realized.
That we would have to adjust that 99 trick and finally 299 299 298 299 Now this is a long complicated solution and it all can get reduced down to one formula the whole thing.
The problem that we have is that 299 is going to break when we get to 72 items and so we go to 399. Then there's a limit.
You can't have more than 32,767 characters in the result of a formula.
So somewhere between 72 and 90 items, this whole thing is going to fall apart.
This is another user voice idea.
If they would add a SPLIT formula, we wouldn't have to use these rogue methods.
Check down there in the YouTube description for this link and give us a vote.
So back to the drawing board I kept saying is there some way that I could on the fly get these things to unwind into a stack - a vertical stack of four columns and then I can send that into UNIQUE.
But everything I tried - well obviously that's not going to work because unique is expecting everything to be in the first argument.
So then I tried the concatenation and that didn't do it at all, and then this awesome thing here with CHOOSE(1 2 3 4 and then trying to choose the right ones.
But it's not concatenating them together, and this is actually the point right here.
I hope that one of my incredibly smart viewers who always down in the YouTube comments, have a better way to do it right here.
I'm hoping that someone will jump in and give me an awesome, awesome solution of how to take these four items and stack them together easily with the formula. Yeah, go.
All right now.
Last Monday I talked about this great new add-in from Charles Williams Version 4 Fast Excel Speed Tools and I'm going to admit I am a Fast Excel Fanboy.
I love these new functions.
I wish the Joe McDaid would give us these functions in Excel. I have these tools and I said I wonder if I can do this?
The SORT of the UNIQUE of VSTACK and then SLICES, SLICES, SLICES, SLICES.
I haven't shown these on the podcast before. Let me just take 30 seconds here.
SLICES is a great little function that says take that array A1 to D12.
I want the entire row.
I'm sorry I want the entire column so that zero says it's not a particular row - I want all of the rows and then cell O4 is 1 and then 2 and then 3 and then 4.
So SLICES will take a slice either vertical, horizontal from an array.
beautiful and then this is exactly was looking for on the last worksheet. VSTACK - vertical stack.
Take those four and stack them vertically.
From there it's a walk in the park, UNIQUE, SORT, and we're done.
So if you have Fast Excel or if Joe McDaid ever gives us this, Then it'll work, but I don't like that.
I have to say *this* comma, *this*, comma, *this* comma.
I'd really love for VSTACK to be able to allow me to say I want 1 2 3 4.
Doesn't work, gives an answer, not the answer I wanted. Or: another Fast Excel trick.
Here is a function called Unpivot. Unpivot, you know we can do unpivot in power query.
Well, Charles wrote an unpivot that just works in Excel.
The hassle is I'm completely cheating.
Because for the unpivot to work I had to point to a row of headers that aren't there.
And a row of column labels that aren't there, so I had to insert this extra row and column for this to work at all.
Once I do the Unpivot.
The third column that I want so the SLICES(,,3 And then UNIQUE and then SORT and that works alright, so this whole conversation was happening between several Excel MVP's and when I showed this one to Charles he's like, well, OK, clever.
(Thanks Charles!) but I actually have a function in Fast Excel version 3 that will do this called LISTDISTINCTS.
And here in the 4th argument By Rows, if you specify False, it does it by individual cells and items and gives us exactly what we want. Kudos to Charles Williams.
Clearly the shortest formula.
If you own Fast Excel or if Joe McDaid ever gives us this now, in the opposite in the spectrum, and Liam - Liam Bastic is my favorite funniest Excel MVP.
He was actually just being a Joker when he sent in this horrible formula.
It's like I don't know why you all think this is so hard. Just type this.
And then you're going to have a headache.
See what that does it as well.
Never going to work.
Well no it is going to work, but no one's ever going to remember it and I can't tell you how it works because I'm not going to take the time to figure out how it works.
Alright, Brad Yundt, if you follow Quora, the Excel questions in Quora - Brad is always answering Excel questions out there.
Every once in awhile I answer an Excel question on Quora and 10% of the time Brad comes along and says well with all due respect to my friend Bill Jelen.
Here is a way that is 10,000 times shorter than his way. Well just last week.
So Brad says, hey, you know, if we just had a split function that could take Ingeborg's Comma-delimited TEXTJOIN and break it apart.
That would solve it and he said you could write your own UDF - user defined function in VBA. 3 lines, 3 lines of code. How cool is that?
And then use the Splitter of the TEXTJOIN.
Send that into TRANSPOSE because of course TEXTJOIN is going across and then UNIQUE and that solution works as well.
While I love all these solutions and I love all of my Excel MVP friends, it shouldn't be this hard right?
So I went out to excel.uservoice.com. Yesterday an wrote up a new idea.
Surprised no one has asked for this, "Allow UNIQUE to operate on a rectangular range", returning all unique values I just created as one vote.
Please click down in the YouTube description this link right here. Go give me a vote.
It would be a nice thing to be able to do so, unless of course someone comes through with something easier.
Now usually I ask you to buy this book as the solutions in this book, but the solution is not in this book.
This question just came up, but the people that came up with this solution.
Yeah, they are in this book, so go check out the book Excel Insights - A Guide to the Best Parts of Excel by 24 Excel MVP's.
Click that "I" in the top right hand corner and hey if you enjoy videos like this trying to solve these weird Excel problems please subscribe and ring that Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
