Video instructions and help with filling out and completing Where Form 2220 Select

Instructions and Help about Where Form 2220 Select

Hello and welcome to the video by Trump Excel I am so mad bun cell and in this video I will show you how to extract data or filter data based on a drop-down list selection in Excel so here I have a dummy data set with the product name in column B sales rep name in column C and country name in column D I also have a drop-down list here in I - with unique country name so there are eight unique country names from this list and the idea is that as soon as I select a country the record for that country should get extracted here in this list so let me show you as soon as I select India all the records for India get extracted here so I have product 1 then I have product 5 then product 8 product 13 and product 20 and when I change the country this latest changes accordingly so I now only have the records for China now you may think that I can do this this thing by applying a filter on this data set but applying filter has two drawbacks first is that I would have to play with the original data but in this case my original data gets intact remains intact and the second is that if in this case when I filter these data set in a separate location my cell reference is continuous so I have China in m4 and m5 and now if I change this to India I would have India in m4 m5 m6 m7 and m8 while in this case India would always be in d4 even if I apply a filter these reference would not be continuous which means that I cannot use this data if I'm feeding this in a chart while I can use this to make dynamic charts or formulas so now let me show you how I have done this I have in this another filled another sheet I have the same data set and I'll show you how I created it but first to create that filter I need a list a drop-down list so to create that let me select this entire data set and try and get the unique countries so I selected and I pasted it here to get a unique list go to data and in room here you have this option remove duplicates click on this this opens the room the remove duplicates dialog box and it gives you a list of columns here I only have one which is column D so it's already selected so I click OK and this removes all the duplicate duplicates values and I only have 8 unique values or eight unique countries here now I can go here to i2 and create a data validation drop-down list I go to data and in data I have radar validation in the data validation dialog box within settings you go to this drop-down and select list and in source you go and select the unique list of countries that you extracted click OK now I have this drop-down list with unique country names let me give it a thick border so that I don't lose track of it now to do what I've just shown you I would need three helper columns so let me show you what these helper columns would do in the first helper column I need the row number for each of these cells so this would be row number one for my data set this would be row number 2 this would be row number 3 so to do this you can either manually hard-coded or even better use this formula rows rows formula takes the input as this array and it returns the number of rows between these two cell references so fraud between d 4 and D 4 there is only one row I will lock this first d 4 by pressing f4 from my keyboard and when I enter this this gives me 1 because from b4 to d4 I only have one row but when I drag this down this would get incremented by one because here from d4 to d5 I have two rows now I will create a helper column to in this helper column I only want those row numbers which matches the country which I select here in I - so for example if here in I - I select India then in this helper column I only want those row numbers which have India as their country so I would want 1 + 5 + 8 + 13 + 20 so to do that I would use a simple if condition in if I first test a condition and my condition is that this selected country and I will lock I - by pressing f4 if this is equal to this country then give me this row number in helper column 1 else give me a blank cell and I press ENTER and this returns a 1 because this country matches my selected country and I can drag this down and you can see it only returns those row number where the country matches the country have selected here so 5 has India it has India 13 has India and 20 has India now I would create a third helper column and here I want all these numbers stacked up together so I don't want these gaps in between I want these numbers one one at a time so I should have one here 5 here 8 here 13 and 20 here so to do that I would use small formula small formula takes two arguments first is the array and kay-kay is the smallest number that you want to return so if I give this array and I give K as 1 then this would give me the first smallest value if I give K as