all right so in this video i'm gonna show you how to do something that i can only probably
describe by just demonstrating it so basically we have this data and we're running our query
statement on top of that data and the whole idea of this is going to be that we have these check
boxes on top of this data and by checking the box we're going to display that column as one of the
results columns and if we want to remove it from these results we simply just go back and uncheck
the checkbox and if i go back and you'll see it's gone so basically i can go back here and check any
columns i want so let's say i uncheck all of these and i'm just gonna check gender and i'll check
vehicle and domain those three columns so if i go back to results you'll see that's going to display
those three columns in the statement and again you can go back and
change to any other columns from this list and they automatically are going to populate
in here and i have this extra space here which is technically not necessary but
that's pretty much what we're going to do in this video so let's get
right to it so this is going to be our data to start with so let's just add a row here
and make sure we have some checkboxes here so i'm just going to highlight
on top of all of these columns
go to data data validation checkbox reject any other input
hit save so far that should give us our checkboxes so that's really all i need here
but to explain what i'm gonna be doing i'm gonna add some space on top for now
and then we'll go ahead and delete this rows once we're done
so let's say we're checking some of these boxes
so these would be the columns we would need to target
so what i'm going to do here i'm going to first start by using this function called column
and what i'm going to do in here i'm going to use an array formula and instead of selecting a single
cell so if i just select the single c gender cell that gives us 1 because it's the first column
but instead i'm just gonna actually select this range of column labels from here up to here
close parentheses and then i'm going to do ctrl shift enter or command shift enter to
basically make this array formula hit enter and what you should see happens well we have all this
formatting problems but let's just change this to something else so now what we have is numbers
see one two three four five six seven eight now if your data didn't start in the first column
and it was like this this would start from two so you would have to account for that
as a matter of fact let me actually clean this up and do it that way
so now this should be the first column so what i would do in this case i would just go in here and
do -1 to account for that so see it goes one or what you could do also you could just say
minus column and then click in this first cell for gender and that should give us basically
2 for this cell which means now i'm going to get 0 1 2 3 but
now what i can do i can get back to this formula and i can just add one to this
and now i'll get one two three four five six seven so the difference between this and the other one
is that for this one you can actually go back and keep adding new columns or you can delete columns
and you'll see that it will stay one two three four five six seven
because now it's more dynamic so at this point we just have this sequence of one two three four five
six seven these numbers so out of these numbers basically we need to just have the ones that are
checked those would be the columns so if this checked this would be column number three that's
one of the columns we need if this is checked that should be call number five and so on so what
i could do here i could do an if function under this if function i'm gonna do a logical test and
basically i want to check if this is checked so i can just highlight this area of checkboxes on top
so if that equals to true basically comma then i want to actually have those column numbers
displayed so i'll just highlight these numbers that i just created using the other formula
just like that comma otherwise i'm just going to leave it blank by doing double quotes like this so
this equals true is not necessary here because these values are already trues and falses in
this checkboxes so instead of doing this equal true we can just keep doing equal true because
what's gonna happen it's gonna give us this trues and falses right here because of our checkboxes so
what i'll do now i'll just convert this to an array function with ctrl shift enter or command
shift enter here we go hit enter what you're gonna see is that because there's a check box here this
three shows up and if i go ahead and check this box well probably wasn't the best column to choose
from because of the formatting let's actually do one of well this one see two uncheck it
this is gone check it it's back so that's what i got so far so what i'm gonna do i'm gonna combine
all these formulas together to a single formula so here what i'll do i'll just replace this
with whatever i have in here so i'll just copy this middle part without array formula
hit escape go back to this formula and replace this range right here with that other formula
that's going to give me that same sequence of numbers basically so if i hit enter i can now
delete this and this should just work on its own and see if i check the box it just gives us the
column number otherwise it doesn't so that's what we got so what i'm going to add to this
instead of just saying 3 5 7 i'm going to change it to col 3 col 5 col 7 which is column 3 column
5 column 7. so what i'm going to do i'm gonna go back to this formula right here and this is
the formula that creates that one two three four five six seven so what i'm
gonna do i'm just gonna add some spaces here just to make this clear they're not necessary
and what i'll do i'll just take this whole thing and put this in parentheses to group it together
and then i'll concatenate it with that word col in front with an ampersand just like this
so if i'd enter right now you'll see that now it's going to be cl3cl5 and if i check this box
it should give us that column two so there we go now we got the columns so what i need at this
point is something that's going to basically join them with a comma in between all of these columns
this is where i can use something like text join and the reason i'm using text join is because
text join has this option to ignore empty which is going to be very useful in this case so my
delimiter is going to be a comma i'll actually do comma and a space then do a comma and then ignore
empty i'm going to say true actually let's just say false so you can see the difference right so
i'm going to say false comma and then the text so the text is going to be basically this range
right in here again i have to match the original range of the formula so starting from here
i'm going to go up to here close parenthesis and hit enter and you'll see now i got all these comma
separated columns but there's also all these blanks in the middle because of these blanks
what i can do i can go back to that same text join formula right here and change this false to true
and what's going to happen now see it's going to ignore those blanks and we're going to get
a nice comma separated list of columns just like that let's actually just cut and paste it here so
it's kind of matching so now i'm gonna again convert this to one single formula so instead
of doing this range in here i'll just go inside of this other function take this whole thing
copied without the equal sign hit escape go back and replace this range
with that array formula that gives me what i need i'm also going to remove these extra spaces
because i don't need those anymore
and then i can delete this one because this is going to work by itself so now if i check this box
see it gives me the next column if i check this box so it's just going to add that column to that
list of columns if i uncheck them they're just going to disappear so what i need at this point
is to add a select statement in front of this whole thing so i'll just go in here and before
this whole text join i'll just do in quotation select i'm gonna need a space and then ampersand
to join this text join operation next to it so now i got this formula that gives me see that select
with this text join with this entire thing in a single piece so now that i got this working i'm
going to create a new worksheet i'm going to call this results i guess and what i'll do i'll just
do cut for this formula so ctrl x or command x go to my results and do control v or command v
so i'm just basically moving it here the only difference in my formula is going to be that
now my ranges are referring to this other worksheet text instead of the same worksheet so
what i can do now i can go back and delete all of these columns on top and just have my checkboxes
and there's my formula that gives me this so now this should be useful in our query function
so we'll just go ahead and add our function right here and i'll go back and select my data
so again that's going to be just a regular range of data from here up to
let's say here i'm going to drop the end reference so we need to make sure this data
range matches the column range we did before comma then i need some sort of select statement
so for now i'm just gonna do double quotes comma and then the number of header rows
is just gonna be one this first row i did not select check boxes as a part of this
so that just gives me everything because this doesn't do anything so what i'm going to do
i'm just going to select this formula copied without the equal sign go back to
this and replace this with my formula right there and you can see i get a value error
and the reason for that is because this column syntax works with arrays and this is a range
so to convert that to an array we'll just take that and put it in curly brackets like this
hit enter and here we go we got the columns that we selected from the other tab so
at this point i can go back and let's say i don't like to have this this i'm gonna uncheck
a few of these and let's say we want the first name the last name the state
and credit card type so at this point if i go back see that's what i got because that's what
i checked here if i just go ahead and click on city i go back city is one of these two
and then we can go to our statement and continue it and make it more interesting so what i'm gonna
do i'm gonna move this to a new line i'm gonna press alt enter or option enter to just separate
this to a new line and also this one too alt enter or option enter this is not necessary but
i guess it just keeps this a little cleaner so we can see what we're doing so this line now is going
to be my select statement so if i wanted to join something to this i could say another ampersand
and then in quotes i could add some filters so i could say where column i'm not sure we'll have
to see which column we want to do let's say we want to filter by this credit card type
so that's going to be column number 11 apparently so we'll go back and say we're call 11.
equals two in single quotes master card so that should filter all of these to mastered
card because of that statement right here and maybe we don't want to see that column
anymore so we'll go back and uncheck this box that should still filter to that card type but
we don't have to look at it basically and we can decide which columns we need to look at
and there it is and that should do it
thanks for watching please subscribe and i'll see in the next one