Another new LAMBDA helper function in Excel is MAKEARRAY. This video shows a simple example, and then tackles the problem from episode 2317 of how to unwind a rectangular range into a single column so you can pass it to UNIQUE and SORT.
The formula used in the video is:
The formula used in the video is:
Excel Formula:
=LET(array,B2:G15,
rowcount,ROWS(array),
colcount,COLUMNS(array),
itemcount,rowcount*colcount,
MAKEARRAY(itemcount,1,
LAMBDA(r,c,
INDEX(array,
LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)),
ROUNDUP(r/rowcount,0))
)))
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2416.
Unique of a rectangular range using the brand new MAKEARRAY array function.
Hey, welcome back to the MrExcel netcast. I'm Bill jelen.
We've been talking about these new Lambda helper functions.
We already talked about MAP, REDUCE, SCAN, BYROW, and BYCOL.
Today I want to talk about MAKEARRAY and when I saw this one, I thought, “oh, let's revisit this nine minutes and 48 seconds of hell getting the unique on a rectangular range”.
It had a lot of methods including this one by my friend Liam. Just a terrible way to go.
Alright MAKEARRAY is very simple. How many rows do you want?
How many columns do you want? And then what's the logic? What's the formula?
For each intersection of row and column.
So here we're doing 12 rows by 5 columns and then the LAMBDA function needs R and C – row and column.
And what the calculation is.
Now, the classic one is R times C to create a multiplication table. But it can be anything.
Two times R divided by C. Whatever you need.
The complex method here and this is the one in the video.
We had a rectangular range of data that I wanted to get a unique of all the items there.
Not on a “by row” basis.
So the question was how to unwind this into a single column so we can pass it to UNIQUE.
And the formula that lets me do that is this one.
So we use the LET function, assign the Array to be A2:G15.
Count how many rows there are using the ROWS function.
Count, how many columns there are using COLUMNS function. And then how many total items there are.
In this case, it's 84 items using RowCount times ColumnCount.
Now here's where it MAKEARRAY pops in.
I'm going to create an array that is 84 columns tall in one column wide.
And then the calculation is going to be a Lambda and pass it R and C.
Now C is going to be one every single time, so we're not even going to use C in this. It's just which row are we trying to figure out.
And then this becomes a nice little dance here of trying to figure out which row we want to get the data from and that's using the MOD function.
You always have to worry about the mod of 14 / 14 is going to be 0 instead of 1, so there's a little if statement here.
If A is equal to 0, then use 14. Otherwise the calculation of A.
To get the column we're going to do our WhichRow here divided by the RowCount.
Rounding up that makes sure that we get to the right column.
And what we get is in the 1st 14 items Orange through - I don't know how to pronounce that is that Xigua?
Orange through Xigua appears and then we start Banana from column two and it unwinds.
Now once we get that whole thing then it's just simple to wrap the whole formula in UNIQUE.
There is a unique list and then to sort that, just add a sort function. Like that and the sorted unique list.
I don't know if this is any better than any of the methods in the nine minute and 48 second video, but the one advantage this video is a lot shorter and you can get on with your day.
If you like these videos please down below, click Like, Subscribe and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Unique of a rectangular range using the brand new MAKEARRAY array function.
Hey, welcome back to the MrExcel netcast. I'm Bill jelen.
We've been talking about these new Lambda helper functions.
We already talked about MAP, REDUCE, SCAN, BYROW, and BYCOL.
Today I want to talk about MAKEARRAY and when I saw this one, I thought, “oh, let's revisit this nine minutes and 48 seconds of hell getting the unique on a rectangular range”.
It had a lot of methods including this one by my friend Liam. Just a terrible way to go.
Alright MAKEARRAY is very simple. How many rows do you want?
How many columns do you want? And then what's the logic? What's the formula?
For each intersection of row and column.
So here we're doing 12 rows by 5 columns and then the LAMBDA function needs R and C – row and column.
And what the calculation is.
Now, the classic one is R times C to create a multiplication table. But it can be anything.
Two times R divided by C. Whatever you need.
The complex method here and this is the one in the video.
We had a rectangular range of data that I wanted to get a unique of all the items there.
Not on a “by row” basis.
So the question was how to unwind this into a single column so we can pass it to UNIQUE.
And the formula that lets me do that is this one.
So we use the LET function, assign the Array to be A2:G15.
Count how many rows there are using the ROWS function.
Count, how many columns there are using COLUMNS function. And then how many total items there are.
In this case, it's 84 items using RowCount times ColumnCount.
Now here's where it MAKEARRAY pops in.
I'm going to create an array that is 84 columns tall in one column wide.
And then the calculation is going to be a Lambda and pass it R and C.
Now C is going to be one every single time, so we're not even going to use C in this. It's just which row are we trying to figure out.
And then this becomes a nice little dance here of trying to figure out which row we want to get the data from and that's using the MOD function.
You always have to worry about the mod of 14 / 14 is going to be 0 instead of 1, so there's a little if statement here.
If A is equal to 0, then use 14. Otherwise the calculation of A.
To get the column we're going to do our WhichRow here divided by the RowCount.
Rounding up that makes sure that we get to the right column.
And what we get is in the 1st 14 items Orange through - I don't know how to pronounce that is that Xigua?
Orange through Xigua appears and then we start Banana from column two and it unwinds.
Now once we get that whole thing then it's just simple to wrap the whole formula in UNIQUE.
There is a unique list and then to sort that, just add a sort function. Like that and the sorted unique list.
I don't know if this is any better than any of the methods in the nine minute and 48 second video, but the one advantage this video is a lot shorter and you can get on with your day.
If you like these videos please down below, click Like, Subscribe and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Last edited by a moderator:
