0613 Advanced Filter - Differential Criteria

4 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.61
You save:  A$55.89
List Price:  ৳11,880.48
You save:  ৳3,394.66
List Price:  CA$186.90
You save:  CA$53.40
CHF 91.49
List Price:  CHF 128.09
You save:  CHF 36.60
List Price:  kr886.90
You save:  kr253.42
List Price:  €119.11
You save:  €34.03
List Price:  £107.12
You save:  £30.60
List Price:  HK$1,084.94
You save:  HK$310
List Price:  ₹10,484.85
You save:  ₹2,995.88
List Price:  RM587.32
You save:  RM167.82
List Price:  ₦52,986.21
You save:  ₦15,140
List Price:  kr1,263.20
You save:  kr360.94
List Price:  NZ$212.42
You save:  NZ$60.69
List Price:  ₱6,873.58
You save:  ₱1,964.02
List Price:  ₨23,542.81
You save:  ₨6,727
List Price:  S$192.23
You save:  S$54.92
List Price:  ฿4,355.29
You save:  ฿1,244.46
List Price:  ₺1,020.43
You save:  ₺291.57
List Price:  R2,475.27
You save:  R707.27
Already have an account? Log In


Hello, as a continuation of the discussion on Advanced Filter, here comes another video. Now, let me show you from scratch how a criteria can be built all it's very simple. What I need to do is I need to pick up let's say a city if I want certain cities specifically, and I pick up Kolkata now I would want that within Kolkata only the ones which are more than hundred thousand dollars should appear in my final output. So I pick up the hitting, copy pasting is the best method because I do not risk writing the incorrect spelling of the header. And I'm going to put a greater than sign or in fact, greater than or equal to and then right maybe one lakh Yeah, that's one 10th of 1,000,001 lakh and now let me proceed the Advanced Filter. So I go to data, I go to advanced.

In here, I first choose Copy to another location, and within which I'm going to delete all the options so that we can start from scratch. Yeah, give me a minute. Now, I place my cursor under list range and I pick up the entire data starting from the header including the header. So shift control right shift CTRL down. Once having done so, I press Ctrl backspace. Now please note Ctrl backspace is not important, it's just to ensure that having the data selected and keeping it So, you are able to go back to the header and see where you began.

So, once I choose in the list range, I go to criteria range and this time I pick up the entire portion. and thereafter I go to copy to and pick up the cell destination cell where the output should begin. So as I press OK, I should be getting only Kolkata B's climbs who are exceeding $100,000 in number. So then, that is how you make an Advanced Filter output. Now, let me do one thing, Let me press Control Zed when once you've applied Advanced Filter, it doesn't Let you press controls it anyways, what I'm gonna do is I'm going to copy one blank cell, choose the entire data block and simply paste it so that it supersedes all the data that we had prepared. Now, if you want multiple differential criteria, for example, you want to find out who are the clients in Kolkata city which are located in park Plaza and in Gurgaon, which are the clients which are located in the building called Shanthi mckeithen.

So this is like one line at a time it reads one line at a time Kolkata Park Plaza, Koreatown Shanti Nikitin similarly, if you had placed your criterias like these under location, Park Plaza more than 70,000 and the location shanton, even more than 50,000 which means this is a differential multiple filter, like me, in fact, use the case three to populate the output and see what we get. So I go to advance. I go to copy to another location. Just for our Our cosmic purpose, our our learning purpose, I'm reflecting the entire data set. So shift control, right shift control down. And what do we need to press to go back to the header?

Huh? Yes, Ctrl backspace key. In the criteria range, I'm going to delete the existing one and choose the case three. So I'll have to move to the right hand side. Yep, here goes, I choose it. So please ensure that you are taking the header in both the list range as well as the criteria range.

And when I go to copy to, ah, I may have made a mistake here. And this is quite natural. Since you're using it for the first time in the criteria range, you must pick up this entire group of cells. And once you've ensured that you've picked up the right range of cells, then and then only you go to copy to and it primarily has to be just one cell from where the data should begin as I press OK. Ideally, I should be getting in few moments per class, which is more than 70,000 and shanty Nikitin, specifically the ones which is more than $50,000 so we'll see more variations of Advanced Filter as we proceed. Till then practice these three criterias Thank you for watching so far

Sign Up