Excel Magic Trick 970: Extract Records Where Number is Divisible by 5 (5 Examples)

Click Show More for time linked TOC.
Extract Records Where Number is Divisible by 5 (or any number) using (Concepts in video good for any data or record extract situation):
1. Filter and Helper column with NOT and MOD function in logical formula (0:29 minute mark)
2. Advanced Filter with single cell criteria logical formula with MOD function and comparative operator (equal sign) (3:04 minute mark)
3. Helper column with SUM and MOD function and INDEX and MATCH function non-array formula to extract records (also IFERROR and ROWS functions) (5:06 minute mark)
4. Array formula to extract records / data with INDEX, IF, SMALL, ROWS, ROW, MOD and SUMPRODUCT functions. (11:03 minute mark)
5. Array formula to extract records / data with INDEX, IFERROR, AGGREGATE, ROWS, ROW, and MOD functions. (18:14 minute mark)

Filter, Advanced Filter, Helper Column formula, Ctrl + Shift + Enter array formula, or non-Ctrl + Shift + Enter array formula?

Array Formula Function Functions

[excelisfun]

Cool! Thanks for the powerful word:

MONUMENTAL!!!

Sounds so rock solid!

[om]

Awesome Video Mike!!!

To all the viewers of this video:

If you can't see the Clipboard when pressing Ctrl + C + C, go to the Clipboard group under the Home Tab and click on the small arrow on the bottom right corner of the group.

After the clipboard has appeared, click on Options (at the bottom of the Clipboard) and enable the option "Show Office Clipboard when Ctrl + C pressed twice"

[ExcelStrategy]

Awesome video ! It summarizes some of the best techniques that extract data from a table !

[excelisfun]

Yes, Advanced Filter can do formulas, and it always seems so strange that you must leave the field name blank...

Thanks for the keyboard coolness!

[excelisfun]

Add another conditional array calculation based on the date column, make sure it is in parenthesizes and multiply it times the other conditional array calculations in the denominator.

[andreabrunellerunburg]

Thank you so much, you saved me many hours and many brain cells by not having to extract by hand!

[excelisfun]

om1812,

Thank you very much for the advice for the viewers!

--excelisfun

[excelisfun]

Try this video:

Excel Magic Trick 942: Lookup Last Shipment Date For Customer, Sorted & Not Sorted Date Column

[TheLincCage]

Great video Mike

I had no idea that Advanced filter could be used with a formula!

Since watching your videos I have become a bit of a keyboard shortcut junkie - I'm sure you are aware of this 1 but for anyone reading...to quickly clear a filter you can just use ALT + DownArrow together on the cell with the drop down, then quickly press C instead of using the mouse.

Cheers

[_chess_master]

You said that the lookup functions will detect the first case of appearance and ignore the subsequent ones. I have a table where I actually need to extract the last instance (I have a list of drawings where the same number and name may repeat many times and only revision will increase by one every time we send and receive the document. How can I apply the advanced filter to exctact the drawings with latest revision only? Thank you

[randomguy]

WOW I needed this channel at the beginning of my semester! You fucking rock

[excelisfun]

No. I am working 16+ hour days and get 100+ e-mails a day. Try posting a well asked question to THE best excel question site:

mrexcel [dot] com/forum

Be sure to set your question up so people can understand and don't reference this video (they haven't seen it).