Today, a deeper dive into the new Excel functions TOCOL and TOROW.
Table of Contents
(0:00) Ignore errors with TOCOL
(0:46) Non-contiguous ranges with TOCOL
(0:56) 3-D Ranges with TOCOL
(1:26) Using TAKE and HSTACK
(2:01) VSTACK for Headings
Table of Contents
(0:00) Ignore errors with TOCOL
(0:46) Non-contiguous ranges with TOCOL
(0:56) 3-D Ranges with TOCOL
(1:26) Using TAKE and HSTACK
(2:01) VSTACK for Headings
Transcript of the video:
Let's take a deeper dive into the new functions TOCOL and TOROW. To column - basically, we'll take a rectangular range or an array and unwind it into a single column.
Yesterday I had kind of missed the fact that out here in the ignore argument there's actually more choices.
I had talked about #1 to ignore blanks.
But there's also 2 to ignore errors and then 3 to ignore blanks and errors.
So right now we just have a zero in there.
If I put in a 1, you'll see that this blank cell right now is becoming a zero.
That will get ignored, which is cool.
But then we also have an #N/A in there which is being returned.
“2” will ignore the #N/A but bring the zeros back It's three to get rid of the #N/As and zeros.
Both of these, TOCOL and TOROW can work on non contiguous ranges.
So here I'm using CHOOSECOLS to get the first column and the last column and it has no problem dealing with that.
This one I never even thought about it until I saw Joe McDaid mention it in response to one of my posts yesterday where someone asked a question.
So I have 3 sheets here: Products, Prices and Costs.
This is the most evil way to setup data.
But let's do a 3D reference here, where I'm getting everything from Products to Prices.
Just the nine cells, so we can see the whole thing.
And we get is the first nine cells from the first sheet and then the nine cells from the second sheet.
Now what do we do with that?
Well, we can do a TAKE get to get the 1st 9 rows and another TAKE to get the last 9 rows.
And then HSTACK to stack those two together.
Then I added a sheet called Cost.
Let's just go crazy here.
So everything from Products to Prices to Cost - those three sheets.
Now I'm going to get 27 rows.
The TAKE just like before.
But then in the middle to get the middle 9, CHOOSEROWS and what do I want?
I want 9 rows, 1 column, starting at 10 and that gets me rows to through 18.
And then to get the last 9, again the TAKE.
Now this is all great, except there's no headings.
No problem with that.
I will just VSTACK an array constant of Product, Price, and Cost back together to add the headings in?
That's a pretty wild formula, right there.
Taking a 3D reference, unwinding it, splitting apart, putting it back together, and adding headings.
Pretty awesome new power that we have here.
As far as TOROW, it works the exact same way, stretching things out in a row.
Every time that I'm tempted to use TOROW, I am like, well, what the heck am I going to do with a row of that data?
I think the TOCOL for me anyway, won't be used much more frequently.
Kudos to the Excel Calc team - these new 14 new functions that debuted yesterday are amazing.
Check out my video there in the top right hand corner for details on the other functions.
Well hey I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Yesterday I had kind of missed the fact that out here in the ignore argument there's actually more choices.
I had talked about #1 to ignore blanks.
But there's also 2 to ignore errors and then 3 to ignore blanks and errors.
So right now we just have a zero in there.
If I put in a 1, you'll see that this blank cell right now is becoming a zero.
That will get ignored, which is cool.
But then we also have an #N/A in there which is being returned.
“2” will ignore the #N/A but bring the zeros back It's three to get rid of the #N/As and zeros.
Both of these, TOCOL and TOROW can work on non contiguous ranges.
So here I'm using CHOOSECOLS to get the first column and the last column and it has no problem dealing with that.
This one I never even thought about it until I saw Joe McDaid mention it in response to one of my posts yesterday where someone asked a question.
So I have 3 sheets here: Products, Prices and Costs.
This is the most evil way to setup data.
But let's do a 3D reference here, where I'm getting everything from Products to Prices.
Just the nine cells, so we can see the whole thing.
And we get is the first nine cells from the first sheet and then the nine cells from the second sheet.
Now what do we do with that?
Well, we can do a TAKE get to get the 1st 9 rows and another TAKE to get the last 9 rows.
And then HSTACK to stack those two together.
Then I added a sheet called Cost.
Let's just go crazy here.
So everything from Products to Prices to Cost - those three sheets.
Now I'm going to get 27 rows.
The TAKE just like before.
But then in the middle to get the middle 9, CHOOSEROWS and what do I want?
I want 9 rows, 1 column, starting at 10 and that gets me rows to through 18.
And then to get the last 9, again the TAKE.
Now this is all great, except there's no headings.
No problem with that.
I will just VSTACK an array constant of Product, Price, and Cost back together to add the headings in?
That's a pretty wild formula, right there.
Taking a 3D reference, unwinding it, splitting apart, putting it back together, and adding headings.
Pretty awesome new power that we have here.
As far as TOROW, it works the exact same way, stretching things out in a row.
Every time that I'm tempted to use TOROW, I am like, well, what the heck am I going to do with a row of that data?
I think the TOCOL for me anyway, won't be used much more frequently.
Kudos to the Excel Calc team - these new 14 new functions that debuted yesterday are amazing.
Check out my video there in the top right hand corner for details on the other functions.
Well hey I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
