No Links were listed yet. Go ahead and share!
Hey, welcome back to the MrExcel Netcast. I’m Bill Jelen. Basically, we start out with massive amounts of data to how we’re going to analyze this. Well let’s fire up the activity table and see if we’ll resolve this problem.
Welcome back to MrExcel Netcast, I’m Bill Jelen. Today’s question comes in from Peter. Peter wants to know how to do a V look up to démodé the results. So he has a setup here where he could choose a product and he wants to then see all of the orders of that product. You can see the orders over here and there was a cross multiple orders, now V look up is going to return the first one.
Somebody suggest that we don’t use a V look up but we’ll use something called, well in the old excels it is called the advanced filter and in the new excel you can find it in the data tab it's in sort and filter and then just advanced. This is a pretty powerful feature and that you can do a lot of different things with it, you can use it to get a unique list of products. In this case we have our database here and I’m going to say that I want to copy to another location. The copy too is going to have the headings order and quantity.
The criteria range is going to be in this place where we have the heading of the product which has to match this heading here. And then the product that’s chosen and we click okay, you see that it goes through and it gives us all of those orders since returning multiple values.
Now in this original set over here, if I would use control T to define this as a table and then I should be able to come back in the data, it ask you a different product and set down C advanced, copy to another location, click okay and you see that we now have the orders for C. Here is the row test where we add a brand new item at the bottom. This is ordered 99999 and comeback into our advanced filter, let’s use Ds here.
Back into our advanced filter and sure enough it was smart enough to grow as the table grew. So I click copy to another location and then okay and you see that we get that new orders so, much better way to go of course you still have to go up and run the advanced filter maybe record a little macro to run that or something like that but better than using V look up because it will return multiple results.
Well then Peter we set in your question in. I want to thank you for stopping by, well see you next time for another net cast from Mr. Excel.
We like to stop by well see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services