now if you're not familiar with this function you should probably
learn about it i'm gonna link to series covering everything you need to know about it but on this
one i'm gonna create a new worksheet here and let's just create a simple statement
so i'm gonna do equals and start my function open parentheses and then we need the data so i'm gonna
go to this worksheet text and basically i need to select this data i'm just gonna go ahead and
select about this much and drop the end reference to go all the way down and then comma and then we
need our select statement so if you wanted all columns then you would do select star
and then you can do the rest of the statement filtering whatever else you have to do but for
right now that's where i'm going to concentrate and then here we're going to do the number of
header rows so just one and that should give us pretty much a copy of that data
now if i didn't want all the columns and i wanted some of them let's say i want gender then
first name and then last name so that would be a c and e then i would go here and do
select a comma c comma e to just get those columns and as you can see that's what i get now the
challenge here is if we have a lot of columns and let's say we want everything starting from a up to
let's say j and then we want to skip these three columns and then get the rest of these going right
there is no way to do a range of columns built in in this function to be able to do that and if
you have 70 or 80 columns all of a sudden you have this challenge you're gonna have to deal with this
crazy function that has all those columns separated by a comma so what we're gonna do
we're gonna get around that problem and there are a few different ways to go about this
one of the ways to go about this is to not use that part of the data at all and basically just
use only the columns you need by using an array so one thing i'm going to go through first here
i'm going to go through changing the syntax so by default the syntax is to list the columns here
but we can also just go here and convert this range to an array
using this curly bracket and at that point if i hit enter i'm going to get an error because with
an array this syntax is no longer valid so now i have to go with column numbers by doing col1 col3
and this is by the way case sensitive so uppercase c and the rest lowercase something like that so
i'm gonna do this and i didn't get one of them right this was probably five not seven
there we go so we got this see now we can refer to this with their column indexes
by saying first third fifth column from this list of columns so one three five so now if i
wanted pretty much to get all the columns except let's say these three columns in the middle i'm
gonna have a couple of options the first option is gonna be if you don't need to use this data
in these columns and you're not filtering by it or doing some other action using these columns at all
then just don't use those in your statement so what i could do i could go back in here
and here when i'm selecting the data i'm going to get back to this and reselect the data and
i'm going to go inside of this brackets go to my data and select basically everything until
that column right here i'm going to drop the end reference like this so this is the first part of
my data then i'm going to do a comma for my array columns and then for the second part
i'm just going to skip those columns i don't need and go to the rest of this data like this
and again drop the end reference to go all the way down so basically now i have this
two different parts of this data c selected in this array like that at this point if i wanted
all those columns that i selected i could just do select star to get all those columns
because i haven't selected these middle ones they're just not going to show up
so if i hit enter see i'm going to get these and then we're going to see how after age it
jumps to our tracking codes which is basically see after age we have our tracking code so that's one
way to do that range of columns without having to list every single column in your statement
and then you could just do multiple different ranges here by simply just comma separating
if you have like different areas from your data you need you can just comma separate all of those
now again depending on your settings those commas might be actually these signs watch
my syntax video for more details about that for now that will be it so you just have to remember
that now you have a new array and in this array if you have to do something with those columns
and filter with those columns you just have to go with that column number and you have to remember
that column number is going to be within the range you selected so what that actually means is that
if you wanted to now have this filtered let's say by occupation the column number for occupation if
you just count from here is going to be see 1 2 3 4 5 6 7 8 9 10 and what you need to do
you need to skip counting these because they're not a part of our data so you would then go 11 12
and that would be column number 12 if you needed to filter that by occupation now the plan b here
is to do something different assuming you may need some of those columns that we're not displaying
but you still want to do this range then what we can do we can build that statement so i'm going
to change this first of all to our original data let's just undo this until i get there so that
should be good enough see this was the whole data from a through t which is basically this
whole thing all the way up to this end so at this point if i wanted to get let's say from here up to
this age column i need something that's going to say col1 2 3 4 5 6 7 8 9 10 all comma separated
next to each other now i don't want to write all of that in my statement so instead what
i'm going to do i'm going to build that so the way we can build that is by using
for example the sequence function so in this function i can do one for number of rows comma
and then let's say for number of columns i do something like four so right now if i
just close parenthesis and hit enter you'll see that what i get is one two three four
and then if i change this to 5 or 3 you can probably guess what's going to happen
so basically i'm just going to get 3 in this case now one other thing here is that after this column
if i do a comma there's also an option to do the starting number so for example
by default it starts with a one so if i just type one it's still gonna go one two three however if i
go and change this one to let's say seven then it's gonna start from seven and it's gonna go
three columns so seven eight nine so again seven is the starting point and three is the
column number so we got three columns starting from seven so i got seven eight nine now in the
case that i was referring to i needed to get from one to ten so what i could do i could do one ten
and because starting point by default is one i don't have to do like a starting number here
so i'm going to get basically first 10 columns so if i do this see it's going to give me a range
of basically 10 like that now i don't need this however i need something that's going to look like
call 1 comma call 2 and comma and so on until i have all of those right and you don't really
need those spaces after those commas but you could also have those spaces if you wanted to
now to do that i'm going to go back to this and i'm going to concatenate that col like this
in the beginning of this and with an ampersand we'll concatenate that text in the beginning with
the sequence and because it's gonna be an array of values i'm gonna have to use ctrl shift enter
or command shift enter to get array formula around this and hit enter and now what should happen see
it's gonna be instead of one two three four five it's col one two three four now we need just comma
separated list we don't need this in separate columns like this so for that i can take all
of this i'm going to just add some spaces to make this clear and use this inside of a function join
and in this function we can provide a delimiter so basically what we want to have as a separator when
we join them so i'm going to do comma and a space and then this comma will be our function comma for
join and then we'll provide this whole sequence thing as the array of values to join so right
now if i enter you're going to see that i got this text that gives me all those columns comma
separated so if you remember if i go back to the statement it needs to say basically select space
and basically all of these columns that needs to be the statement over here so what i'm missing is
that select part in the beginning so to add that i'm going to go back to this and right before this
join i'm going to in quotations to select and we need a space after the word select and then
we're going to use ampersand again to concatenate that joint statement to that select so now if i
hit enter see i got select and all those columns so at this point i could just take all of that
copied without the equal sign
and i'm going to delete that actually from here go back to this statement and replace this
with that function i'm going to hit enter and that should give me everything from column 1 up to ten
as you can see so i'm going to change that from one to three for now or four
that should give us the first four columns if i do that see i'm gonna change it back to three
i'll come back and add 10 to this but what i want to show you is now let's say you also need to get
these columns in the end so that would be if this was 10 i guess this would be 11 12 13 then 14
15 16. so we need now 14 15 16 17 18 basically until we get to this so for that
we need something that starts from 14 so we're gonna do sequence and number of rows is gonna be
one and we're gonna have some number of columns for now i'm going to just type something like
4 and then we're going to start from 14. so right now it's going to start 14 15 16 17. so assuming
i got this number right and this was actually 14 then if i go from here then we need
seven columns to get to the end so therefore we would have to do one to seven seven columns
starting from 14 so that should get us this and then we're gonna continue doing the same
thing i was doing before i'm gonna do that col concatenation to have the column we're to do our
control shift enter or command shift enter and hit enter that should give us these columns
and at that point we also need to have our join function to put this all together so i'm going to
do join and join it with a comma and a space like this comma and then close this whole thing in the
end so that should give us c from column 14 up to 20. so now i'm gonna take that formula copy it
get rid of this go back to that right here and we need to basically add that to this existing one
that we already had that goes up to here now we don't really need two array formula functions here
but since i copied it i'm just gonna keep it so we're gonna go in here and do an
ampersand to again join that and i'm gonna paste that second array formula function
in here so if i had enter right now well i got a value error let's see what we got so see it says
we have col3 and then col4 it doesn't understand what this means
and the reason for that to show you what happened there i'm just gonna copy
this part of this formula and just put it in a separate cell so you can see what's happening
there and why this isn't working right now so i'm just gonna put this in here so you'll see
what happened is that as i was joining those things we got the first part here right so that
goes column one to three and then we should have a comma before we go column 14 15 16 17
but we don't have that comma right now so we get an error because this is an invalid column so to
fix that when we join these things together we need to add that comma in here so after
this ampersand here i'm gonna do a comma and do another ampersand to join the rest of the stuff
so now see we got an extra comma here just to make this nice i'm going to add a space
in there too within those quotes just like that so now we got this and again to make this a
little more clean i'm gonna use one array formula instead of two so what i would have to do i would
have to find the closing of this array formula so if i just click on this opening see this is
the closing right there that parentheses so i'm going to remove that parentheses right there
and then i'm going to also remove this opening array formula for the second bond so that should
now take care of this and just have one array formula around this whole thing and it's still
gonna work so at this point i can just copy this go back to this one and replace this whole thing
with my formula that gives me that range and if i'd enter see i get
starting from our tracking codes getting back to this end and if i wanted to also get the
first 10 columns here instead of the first three i can just go and update this 3 to 10
and that should give me that
and that should cover that thanks for watching please subscribe and i'll see in the next one