Excel Magic Trick 1325: MIN & MAX Functions For Alphanumeric / Text Values Report (8 Examples)

preview_player
Показать описание
Download Excel Files:

See how to create a report that require that we find the MIN and MAX Alphanumeric Academic Quarters for each Student ID. See eight different methods including: 1) VLOOKUP, 2) MIN & IF Array Formula, 3) AGGREGATE function, 4) MINIFS function, 5) MIN & IF & MATCH non-Helper Column Array Formula, 6) Access Query, 7) Power Query, 8) SQL:
(00:17) Introduction To Problem of finding MIN & MAX Alphanumeric/Text Values
1. (01:42) VLOOKUP: 1) No Helper Column & 2) Columns Sorted (Any Version of Excel)
2. (05:35) MIN/MAX & IF Array Formula with INDEX & MATCH Helper Column: 1) Helper Column Okay & 2) Columns Not Sorted (Any Version of Excel)
3. (11:37) AGGREGATE Array Formula with INDEX & MATCH Helper Column: 1) Helper Column Okay & 2) Columns Not Sorted (Excel 2010 or later)
4. (11:53) MINIFS/MAXIFS, INDEX & MATCH Helper Column: 1) Helper Column Okay & 2) Columns Not Sorted (Excel 2016 or later)
5. (12:18) Excel Lookup Array Formula MIN/MAX & IF & MATCH Array Formula: 1) No Helper Column & 2) Columns Not Sorted (Any Version of Excel)
6. (17:22) Access Query made in Design View, then Import into Excel. MIN and MAX functions can handle Alphanumeric/Text Values. (Any Version)
7. (21:51) Power Query (Get & Transform). MIN and MAX functions can handle Alphanumeric/Text Values. See Group By feature and Add Custom Column Feature. (Excel 2010, 2013, 2016).
8. (27:02) SQL in MS Query Data Import feature. MIN and MAX functions can handle Alphanumeric/Text Values. (Any Version)
(30:48) Summary
Рекомендации по теме
Комментарии
Автор

Im from mexico and i woud like to say: INCREDIBLE VIDEO AND WAY TO TEACH US ! PERFECT EDITION AND ACCENT SO CLEARLY TO UNDERSTAND ! I GIVE YOU MY MONEY NOW ...

IIbarberII
Автор

You are an awesome teacher, specially your accent is so clear for people like us. I also like your special sound effect

zaighamuddinfarooqui
Автор

This is impressive lesson, Mike. Thanks a lot for that !!!

BillSzysz
Автор

SQL results are different....great video

maneshzaveri
Автор

Hello,
I watch numerous videos that you have produced for Excel. I have a request for you, if you don't mind. Can you please tell me, where can I get a video on Cpk and Gage R&R spreadsheets for Excel? Keep up the awesome work!!!


Thanks

flhines
Автор

4 column calculate the sum and more than 5 then 500 and below 5 then 0 in one cell suggest excel formula

shindenagnath
Автор

Thanks for awesome video.I am learning from your channel.
I want to know that, why any one use MS Access instead of MS Excel???

KishanDobariya_KD
Автор

would you please give me link of First in first out (FIFO Method) based calculation by excel function. for share, food item, or any other product... hope u will give me your link for my this problem..

ronyshafiq