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

Показать описание
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
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
Комментарии