Highline Excel Class 19: Advanced Filter Extract Data 9 Examples

[Batooony]

I hate excel. And it'd be pretty lame if I fail college because of it.

[chiefs2k6]

i have a question that may be very simple but i cant figure it out, in my criteria i need codes that are Less Than or Equal to 7, so i put in <=7 in the chart but it doesnt work out right, then i tried <7 and it gives me only codes less than 7 not including 7....what is the notation for Less Than or Equal to?????

[ExcelIsFun]

@chiefs2k6 , Less Than or Equal to is:

<=7

You got it right. I am not sure what is causing your trouble.

[clemopcl]

terrible sound (or pronounciation? "if you want to download mmmpfffmpf, click on mffffffpmmmmmffff mmppp, then click on hmmmpffff hhmmmm mmmm"). I gave up after 1 min.

edit: the vid is nicely done, i intended to run it with sound off, but when using headphones (instead of hi-fi with a little bass), I got every word.

[ExcelIsFun]

@clemopcl , get workbooks at:

people.highline.edu/mgirvin/Ex­celIsFun.htm

[bodrulm1]

Excellent video. I didn't know how to use logical formulas with Advanced filter and now I do. Your videos are super

[ExcelIsFun]

@bodrulm1 , I am glad that the vid helps!

[danianla90]

i don't hear

[Lincenas]

Hey Mike another brilliant Vid! But what if list range for advanced filter is spread accross 3 different workbooks? I can only insert one range there... Cheers :)

[ExcelIsFun]

I don't have a good answer for hat one. Try posting to the best Excel question site:

mrexcel[dot]com/forum

[spurdiman]

I don'tunderstand how to work advance filters in excel 1 2007.

walkingman49@yahoo.com

Tim D

[txvivian2008]

hi, Im trying to extract a record and it's not doing anything. COuld someone please help.

[whiteboyo1212]

Thanks so much haha. Really helped me out with my Mid-level Exercise! THX!!!

[ExcelIsFun]

You are welcome!

[ll1979]

Can anybody Help?? Which formula do i use if i would like a particular cell to always equal to the first cell in the first row once the auto filter has been used? So the cells info will change depending on what is in the first row, hence what i select in the sheet using the drop down box (auto filter). I am making an invoice and would like the Inv# to change on top of the sheet depending on the customer i select.. Can anyboydy Help Me?? Thank you!!

[ExcelIsFun]

maybe this video:

Excel Magic Trick 334: VLOOKUP & Data Validation for Invoice

or:

Highline Excel Class 07: VLOOKUP function formula 7 Examples

[ll1979]

@ExcelIsFun Thank you so much!! I will take a look. Another quick question, can we use "=" and text in an If formula? Ex. =IF(A1=Euro,=A20,0) - I tried, but it doesn't seem to work...

[ExcelIsFun]

No, if you ant to put a formula in a cell with a formula you have to use the CHOOSE function - try these videos:

Highline Excel Class 46: CHOOSE function 4 Examples

or

Excel Magic Trick 557: CHOOSE function to lookup Formula or Function (2 Examples).

[Jaderoccs]

Love this lesson. Is there a way to automatically update an advanced filter? I have created an advanced filter of a sales list and only the items to purchase are filtered to a new worksheet. This works nicely, but when I update the fields, I can not update my new list. Also, I only want certain columns in my list - is this possible?

[ExcelIsFun]

If you have new criteria, run the Advanced Filter again. If you want just some columns, then try highlighting just those columns when creating the Advanced Filter. If you have non-adjacent columns, then you must switch to formulas – much more complicated. If you go to my playlists, I have a play list for “Extracting Data” – there I have many videos about extracting data with formulas.

[ghm2011]

Ahh, thank you. I was trying to figure this out reading the convoluted language in my text and looking at screenshots. I just wasn't getting it. This got me there in 2 seconds.

[ExcelIsFun]

I am glad the video helps!

[theMilosr93]

There's a easier way to do that last trick. You wanted to extract every 10th transaction. You just need to have all the transaction cells set as text (in format \ cells \ number - select text) and also the criteria cell set as text. In fieldname tupe Transaction or whatever your header cell name is for the numbers,and for the criteria type in *0 (this won't work if the cells are not selected text in format \ cells \ number). You probably knew all this, I'm just trying to be smart. Great video.

[ExcelIsFun]

Thanks for the tip!

[Markblackburn86]

HI Mike, how would I go about extracting a date range i.e. Dates are in column "A", and the needed criteria is >2011/1/17 and <2011/1/20?

[ExcelIsFun]

Try this video:

Excel Magic Trick 703: Extract Records Multiple Criteria (AND OR logic) Filter, Formula, Adv. Filter

[wahlocki]

Hi, quick question.

I have a list range over multiple work sheets with a common criteria range, can I run a single advanced filter? Or does the list range need to be on a single sheet? thanks

[ExcelIsFun]

I do not know how to run multiple tables through one advanced filter. Try posting questions to:

mrexcel..com/forum

Maybe someone at that site can give you some VBA code to do it..

Send me link after you post! Then I can learn too!

[LuvSpongy]

Excellent! how do I group a range and have it equal a value. For example, I have a range of rows that i want to group as engineers, and I want that group to have a budget. II set up a column called budget with values, How can I set that up?

[ExcelIsFun]

I don't know.

Try the best "ask Excel question" site around:

mrexcel..com/forum

[mmilos11]

GREAT JOB!!!!!!!!!!!

[ExcelIsFun]

Thanks! I am glad that the video helps!

[swmod52]

This does not work at all for me. Do you think I have defective version of the spreadsheet? It says Extract range has missing or invalid field range. What is an "Extract Range"? I don't hear you refering to an "Extract Range"

Now I got it to work without getting the error but it did not filter anything. I just got all the rows to the new place

[ExcelIsFun]

All the rows in a new place means that there was no criteria or that all the records matched.

[swmod52]

This does not work at all for me. Do you think I have defective version of the spreadsheet? It says Extract range has missing or invalid field range. What is an "Extract Range"? I don't hear you refering to an "Extract Range"

[ExcelIsFun]

Extract Range is the "Copy to" text box in the Advanced Filter dialog box.

[gandukabacha]

Hi Mike,

can you explain if can use the simple filter for one criteria , and and or as excel 2007 option to choose multiple commands....so whats the basic difference in the first three examples....

[ExcelIsFun]

I do not understand what you are asking.

[gojilover]

Someone should erect a statue in your honor ..

[ExcelIsFun]

Thanks. That would be cool if they made a statue for some guy that is just having fun with Excel!

[udanial]

sir u r awesome n i mean it, sir, for me u r mrexcel as well as excel "guru" i ve learnt

a lot from ur videos.from where did u learn all this can u revel ur secret?:) n sir i need ur email address bcuz i want to ask u something n its a big question i ll send u excel sheet for solving dat problem can u give me? i ll be very thankful to u.

[ioneveu]

HI, just a quick question:

does the * sign also works when using it in defining the criteria for the advanced filter?

[ExcelIsFun]

Yes. If you enter the criteria W* for a field that contains West and Win, it will extract all records that contain West and Win.

[simabah]

Dear Mike,

On the second example I ran into trouble when I wanted to try the AND function for multiple criteria ranges which includes between 2 greater 30,000 and less than 50,000. my criteria field names aren't exactly like the column names in the original data base because I get to have one extra column which causes the last column to move to the right and mess up the order. How do I handle this? Thanks in advance.(advanced filter that is. :D)

[ExcelIsFun]

I don't understand exactly what you are asking, but if the creteria field names and the table field names are not exactly the same, it will not work.

[simabah]

The data base has 6 columns. because the criteria range is to extract the value between 2 values I am adding a new column to the criteria range so my criteria range is now 7 columns.

I have to add the column because I need to extract the data that is betwee 30,000 and 50,000 and these 2 need one column each, don't they?

[simabah]

I am trying to practice what you mentioned in this lesson. (minute 3:26 to 3:50)

[AmerEzzadeen]

Sir,

I've extracted a data from a raw data set. However, there is a new update on the raw data. How do I update my extracted data so that it reflect the new changes? Or do I have to re extract them?

thanks

[ExcelIsFun]

You have to extract again.

[cpbp09]

hi mike,

your classes are excellent. I was trying to work on your workbook but some of the colums like CoGS and date shows a number sign instead of the actual date or $. can you please tell me how to overcome this ?Thanks

BP

[ExcelIsFun]

Increase the column width.

[cpbp09]

thank you very mucn

[ExcelIsFun]

You are welcome!

[dullknife123]

you are the best .... would you happen to know how change the color of active cell. For example i would like to make it yellow and keep it yellow when i move about the worksheet

[ExcelIsFun]

You would have to do it with VBA code. I am not good with VBA code. You should post your question to the Mr Excel Message Board. Search for and watch this video about this amazing site:

How To Post Questions At The Mr Excel Message Board

[planiolro]

I would like to extract something using advanced filter but I do not know how to write the criteria since I have to extract something which was bought after 2nd of February 2009. How do I write the criteria >02.02.2009?Is that correct?

[ExcelIsFun]

Dear planiolro,

Try:

=">"&DATE(2009,2,2)

Database functions can handle >02/02/2009, but normula formulas cannot becasue they treat the "/" like division.

--excelisfun

[soulsaver04]

dear excelisfun

can i use an AND formula in a cell for my criteria for advance filter ???

thanks ^_^

[ExcelIsFun]

Dear soulsaver04,,

Yes. If you are using field names in cells, just use two field names. For example list "Sales" once then again; "Sales". If you are using a formula, you can use the AND function.

--excelisfun