While working on video 2500, I ran into something that seems buggy with the WRAPROWS function in Excel. Is it a bug? Or a Feature?
Transcript of the video:
I think there's a bug in this new WRAPROWS function.
Where is it by design? Let's take a look.
Okay.
So wrap rows will take an array and wrap that into columns so if we did wrap rows some array and then four, it's going to take the items, there's 11 items in this array and arrange it into four columns and then go to the next row and then go to the next row.
Thanks to this Quote-Quote out here and Pad_With, I avoid the NA error, so it seems to be working.
Like with 13, we get 4, 8, 12, and then the next row 13, 9 works great, 5 works great.
But then something really weird happens when I go to 4.
When I go to 4, instead of getting 1, 2, 3, 4, going across it changes and gives me a vector like that, 1, 2, 3, 4.
3, 2, 1, none of those, I guess we could argue that 1 is working, but 2, 3, and 4 definitely seemed to be buggy to me.
I was actually working on video 2,500 when this popped up and I'm like, "What the heck is going on?" So I asked a few people what they thought and, "It seems to be buggy," they said.
But someone pointed out they actually went and read Excel Help.
Who reads Excel Help? Okay.
In Excel Help, there's a statement there that says that if the Wrap Count is greater or equal to the number of elements in the vector, if the Wrap Count.
So if 4 is greater than the number of elements in the vector 3, let's say, then the vector is simply returned without reshaping it.
So if it was a vertical vector, 1, 2, 3, it just returns the vector.
I'm not sure that that's really what I would expect it to do.
So at 5 it works, but at 4, instead of wrapping across, it wraps down.
All right.
So my solution to this was not a pretty solution, but I'm kind of jazzed because when the EXPAND function came out, I'm like, "I don't know when I would ever use that". Well, here today, August 27th, I get to use the expand.
First thing I do is let the variable A be equal to the SEQUENCE of 4, and then the variable B is the expand of A, and then how many rows do I want to expand it to?
What's funny, I have to go at least to 5 to prevent this weird behavior from happening.
But if it's more than 5, like if it's already 11, I don't want it to be 5 so I have to take the MAX of 5 and how many items are in the array, and then the number of columns to expand to.
I'm not going to change that.
Then Pad_With is what to fill those empty cells with. So if I choose 2, this is creating an array of 5.
You'll actually see here that the array that's being returned is two rows tall.
This seems to be the only way to get it to wrap those small arrays, like 3 and 4 correctly to go across.
So if that's important to you, in the previous video, which I don't know when I'll post, I'll get around to posting the previous video next week, I'm going to have to use this workaround to prevent the array that I want to go horizontal from just switching to vertical at 3 or something like that.
Well, hey, this weekend, I'm shipping Wyn Hopkins' new book on Power BI. It's called Power BI For The Excel Analyst.
The first Power BI book that's really written exclusively for people who are using Excel.
Shout out to Mike Girvin, his Microsoft 365 The Only App That Matters has a book-length section all on Power BI. Certainly, that is aimed at Excel folks as well.
It's a full color book. We have early copies.
This doesn't come out at Amazon until November 1st, but I snagged three boxes from the printer and we shipped one-and-a-half boxes yesterday.
So if you were watching this click, that eye on the top right hand corner and free shipping to the USA.
For people outside of the USA, we're just going to have to wait until the books get to your local Amazon, and then buy there on November 1st. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Where is it by design? Let's take a look.
Okay.
So wrap rows will take an array and wrap that into columns so if we did wrap rows some array and then four, it's going to take the items, there's 11 items in this array and arrange it into four columns and then go to the next row and then go to the next row.
Thanks to this Quote-Quote out here and Pad_With, I avoid the NA error, so it seems to be working.
Like with 13, we get 4, 8, 12, and then the next row 13, 9 works great, 5 works great.
But then something really weird happens when I go to 4.
When I go to 4, instead of getting 1, 2, 3, 4, going across it changes and gives me a vector like that, 1, 2, 3, 4.
3, 2, 1, none of those, I guess we could argue that 1 is working, but 2, 3, and 4 definitely seemed to be buggy to me.
I was actually working on video 2,500 when this popped up and I'm like, "What the heck is going on?" So I asked a few people what they thought and, "It seems to be buggy," they said.
But someone pointed out they actually went and read Excel Help.
Who reads Excel Help? Okay.
In Excel Help, there's a statement there that says that if the Wrap Count is greater or equal to the number of elements in the vector, if the Wrap Count.
So if 4 is greater than the number of elements in the vector 3, let's say, then the vector is simply returned without reshaping it.
So if it was a vertical vector, 1, 2, 3, it just returns the vector.
I'm not sure that that's really what I would expect it to do.
So at 5 it works, but at 4, instead of wrapping across, it wraps down.
All right.
So my solution to this was not a pretty solution, but I'm kind of jazzed because when the EXPAND function came out, I'm like, "I don't know when I would ever use that". Well, here today, August 27th, I get to use the expand.
First thing I do is let the variable A be equal to the SEQUENCE of 4, and then the variable B is the expand of A, and then how many rows do I want to expand it to?
What's funny, I have to go at least to 5 to prevent this weird behavior from happening.
But if it's more than 5, like if it's already 11, I don't want it to be 5 so I have to take the MAX of 5 and how many items are in the array, and then the number of columns to expand to.
I'm not going to change that.
Then Pad_With is what to fill those empty cells with. So if I choose 2, this is creating an array of 5.
You'll actually see here that the array that's being returned is two rows tall.
This seems to be the only way to get it to wrap those small arrays, like 3 and 4 correctly to go across.
So if that's important to you, in the previous video, which I don't know when I'll post, I'll get around to posting the previous video next week, I'm going to have to use this workaround to prevent the array that I want to go horizontal from just switching to vertical at 3 or something like that.
Well, hey, this weekend, I'm shipping Wyn Hopkins' new book on Power BI. It's called Power BI For The Excel Analyst.
The first Power BI book that's really written exclusively for people who are using Excel.
Shout out to Mike Girvin, his Microsoft 365 The Only App That Matters has a book-length section all on Power BI. Certainly, that is aimed at Excel folks as well.
It's a full color book. We have early copies.
This doesn't come out at Amazon until November 1st, but I snagged three boxes from the printer and we shipped one-and-a-half boxes yesterday.
So if you were watching this click, that eye on the top right hand corner and free shipping to the USA.
For people outside of the USA, we're just going to have to wait until the books get to your local Amazon, and then buy there on November 1st. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
