Excel Magic Trick #126: Macro: Advanced Filter Extract Text

[MrStrewman]

just wanted to say thanks for quebec. Very usefull. Can u do more for humain ressources and dashboard please. Thanks for sharing your knowleadge

[ugbari]

brilliant work

[daggerdotmil]

HI this tutorial was great i used this creating a library for forms but i was wondering is there anyway it also can carry the Hyperlink on the text I hyperlinked the text but when advance filter copies it to new location it is not hyperlinked

[ExcelIsFun]

I do not know. I am not very good with hand written VBA code, but I am sure it can be done. Try THE best Excel question site:

mrexcel[dot]com/forum

[lalaalala1]

Hi I have a question here,

I successfully done something similar to what was shown in this tutorial. However, on top of this, let's say if i want to directly hyperlink each title to their respective videos on my hard disk, the link only works for the full list, but not for the filtered list. In short, the hyperlinking is not retained. How can i solve this problem such that my filtered results STILL has a working hyperlink?

[emailuznow]

Hi Mike, how can i extract all the 3 columns instead of just the title column?

If i select all 3 columns it works fine only the first time when the macro is run however when you run the macro again it only displays the first column and not extract all 3.

[ExcelIsFun]

How about not doing it with a Macro, but instead just using Advanced Filter. I am not sure what the problem is and I am very busy with work right now and sick at the same time...

[wlakshithaw]

Hi,

as you instructed to mond118 i selected the all 3 rows (B-D) and did it. first time it happens. there after when i run the macro only the first column changes. other remain as it is.

but when i copy it to a seperate location ( copy to box) it again works fine..

[ExcelIsFun]

The quick and dirty way to fix it, is to delete the Macro (Alt + F8, then highlight macro, then hit delete button), then create a new Macro. The advantage is then you don't have to edit the code in the VBA editor.

[stacybsimmons]

Hi,

I really like this tip. I'm trying this with excel 2003.

I have setup a spreadsheet similar to the one in the video. When I do the Data Advance Filter the only thing that outputs is the column heading (i.e. Title) No records are output. Any ideas about what I might be doing wrong?

Thanks.

[ExcelIsFun]

Dear stacybsimmons,

Any time you get a zero or blank answer from Advanced Filter, it means that no records match. So eitherno records match or the criteria is not correct.

--excelisfun

[stacybsimmons]

Hi,

Thanks. I figured out what I was doing wrong. I left the $ sign out of the Isnumber(search(f12. Thanks again! Your tips are GREAT!!

[ExcelIsFun]

Dear stacybsimmons,

EXCELlent!!!!!

--excelisfun

[ExcelIsFun]

Usually when a query results in zero records extracted, it is because of:

1) There are no records that match the criteria

or

2) The criteria is not entered correctly (such as an extra space that makes that criteria not match the elements in the table - the reverse can be true also), or the formula is not setup correctly.

Beyond that, I have no idea without seeing the file. Since I do not do consulting, try THE best Excel site for questions:

mrexcel[dot]com

[ExcelIsFun]

If you do post to:

mrexcel[dot]com

Do not reference this video. But instead explain your problem and the data setup and what you have attempted in complete detail. If you ask a question clearly, you can usually get a reply from someone within 15 minutes.

[mutony9]

Hi ExcelisFun,

In my excel data base I have many columns. 3 of which represent the current date. There is a column "d" for day, "m" for month, "y" for year. Each of these columns has an auto filter so I can select which day,month,or year to view. Can I make a button that, when pressed, will filter me the entries for the current date (and to toggle 1 day forward if possible)? Any existing lessons pointing me in this direction would be greatly appreciated!

[ExcelIsFun]

Dear mutony9,

Excel 2007 rules over any other version for many reasons. On reason in the Filtering feature that has automatic today, tomoorw and yesterday. If you wanted to do it with a formula like in this video and you had date column, you could use the formula =Today+A1, then put -1 in A1 for yesterady, 0 for today and 1 for tomorrow. You could create a column with Date from your separate column if you used the DATE function.

--excelisfun

[mond118]

sir..

can u extract all 3 columns together? the video only extracted the TITLe column.. not including the 2 other column..

[ExcelIsFun]

Dear mond118,

Yes, instead of just the B column, use the B - D columnns for the "List range" in the Advamced Filter dialog box.

--excelisfun

[PrincessWithSkills]

This one is quite useful, thanks!

[ExcelIsFun]

Dear PrincessWithSkills,

I am glad that it is useful for you!

--excelisfun