Decline Dropdown Field with airSlate SignNow

Decline dropdown field

hi in this video I'm gonna show you how to get a unique list from a table and bring it in where I can put it into a drop-down list alright so I have my table here we have some repeating items like item 3 and item 5 we want to bring it in to another table where we just take the unique items out of that and it'll help populate this selection item so this is a count function here so maybe I want to count how many item fives are there you can see there's 4 of them if I look at item 5 here just like that right click right click and go under filter this liquor sales value you see there's five of them there let's clear this or if we want to see how many item tens there looks like there's five or four let's go and check that out how many item tens are there click on that there's five item tenths so this is using a data validation feature and to bring this into the table here I'm going to use something called powered quarry now this is Excel 2016 Excel 2016 has power quarry already built in in the data tab you can see it's in the get and transform group if you have previous versions of excel like 2013 to 2010 you're gonna probably have to go to Microsoft comm and download it and install it to enable it but since this is - 2016 let's see how this is done I've selected this here let's actually let me select this again ctrl a and then ctrl C to copy I'm gonna copy it on to another sheet to start fresh Here I am in a new sheet here home we're gonna paste just the values so we're starting fresh I want to turn this into a table oops looks like it didn't copy the header so I'll just insert that right-click insert this will be item and this will be quantity so what as I mentioned before what I want to do is use the power query feature I can turn this to the table with the power query feature ready are you going to data and select from table what's going to do is it's gonna recognize this range of data and want to create a table and my table does have headers the first row or header fields click OK and it's going to bring up the power query editor so what the power core editor does it enables me to do some transformation I don't need this quantity field so I'll just click it and right-click and select remove and here I want to have unique values so I'll just right click and go to remove duplicates they'll have unique values let's also sort it I'll click on the drop down and click sort ascending so you see that when it sorts it since these are strings item 10 is going to come right after item 1 but that's okay this is just for the purpose of bringing in these particular values as unique bag so since we have our unique values I'll click close and load and by default it works it into a new worksheet but let's bring it into our sheet 1 and I'm gonna right click here click select load 2 and let's bring it back to the existing worksheet what I probably need to do right now is just make it load as a connection only click load and it kind of disappears from this worksheet and I'll just have it read load into sheet 1 here so now it's connection only let's make this load into a worksheet click right click click load 2 and let's table we're gonna put it over here existing worksheet select this area let's go to e click OK and now and now click load let's see if it takes care of that yep so now I have item or unique list here and what I want to do now is I can type let's make our header for our drop-down list item and then we'll do the same thing we had counting so now we want to have a drop down here so the drop down is a data validation feature so I'll go under data and under data validation we're gonna select list so we're gonna allow a list and our source is going to be e 2 to e 13 click OK and now we have our drop-down so I have my items 1 10 and 2 let me put my formula here let's select 1 let's try just like item 1 and for our count we'll make the count functions count count if click that so our range here is we can select from a 2 all the way down to a 30 but since this is a table you'll notice that when I selected this table range here it gave me this particular value this argument instead of saying a 2 to a 30 it gave me this table and then in brackets item so this is the nice thing in a way about tables is it tells you the table name this table has a default name called table 1 you can also change the name later on and it's going to look at the item column right so as the table grows or shrinks it's always going to be able to adjust for the values there that's kind of nice thing about table feature now that's my range my criteria comma is going to be cell this cell press ENTER and it's gonna count to 2 of item 1 so if I select item 1 right click and go to filter item when there's two let's say let's clear this filter here and if we go to item 5 again it's gonna give us back that number I think that was for yep so we have 5 right click and we'll see that in item 5 there are 5 entries there right so that's how it's done so as I mentioned four tables are kind of unique you can see here whenever I click on the table you see that there's these table tools contextual menu and you can go and head and rename your table here the same for this one this is power query actually created a table for this too and so even though this is a table where you can rename its table one underscore two this is also part of the query feature now the nice thing about this is if we change something here let's change this to item make it something crazy item zze right and there's only one item as Ezz if i go to data and click on refresh now you'll notice it shows up here and not only does it show up here because of the power query it shows up here because this is referencing this range and it adjusts right so I can select that and it tells me that there's a count of 1 there so that's a nice thing about using tables and power query so that's how you can create a unique list out of a table and use it for a drop-down if you've got any other formulas that you want to use for this particular scenario so I hope that helps thanks for watching [Music]