Excel Magic Trick 1325

MIN & MAX Functions For Alphanumeric / Text Values Report (8 Examples)

DESCRIPTION

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
(01:42) VLOOKUP: 1) No Helper Column & 2) Columns Sorted (Any Version of Excel)
(05:35) MIN/MAX & IF Array Formula with INDEX & MATCH Helper Column: 1) Helper Column Okay & 2) Columns Not Sorted (Any Version of Excel)
(11:37) AGGREGATE Array Formula with INDEX & MATCH Helper Column: 1) Helper Column Okay & 2) Columns Not Sorted (Excel 2010 or later)
(11:53) MINIFS/MAXIFS, INDEX & MATCH Helper Column: 1) Helper Column Okay & 2) Columns Not Sorted (Excel 2016 or later)
(12:18) Excel Lookup Array Formula MIN/MAX & IF & MATCH Array Formula: 1) No Helper Column & 2) Columns Not Sorted (Any Version of Excel)
(17:22) Access Query made in Design View, then Import into Excel. MIN and MAX functions can handle Alphanumeric/Text Values. (Any Version)
(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).
(27:02) SQL in MS Query Data Import feature. MIN and MAX functions can handle Alphanumeric/Text Values. (Any Version)
(30:48) Summary