Excel Magic Trick 1301: Sorted List of Modes in Single Cell with TEXTJOIN Array Formula

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

Learn how to create an array formula to show a sorted list of modes in a single cell. See the Functions: TEXTJOIN, INDEX, SMALL, MODE.MULT, COUNT, ROW, INDIRECT. Learn about difference (and history) between the MODE, MODE.SNGL and MODE.MULT functions.
  1. excelisfun
  2. Excel
  3. Microsoft Excel
  4. Highline College
  5. Mike Girvin
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:07:35

Excel Magic Trick 1301: Sorted List of Modes in Single Cell with TEXTJOIN Array Formula

Excel Magic Trick 0:04:16

Excel Magic Trick 1315: Create A Unique Sorted List with Power Query

Excel Magic Trick 0:04:21

Excel Magic Trick 1300: List All Modes in Single Cell with TEXTJOIN & MODE.MULT

Excel Magic Trick 0:07:12

Excel Magic Trick 1305: Adding Multiple Two-Way Lookups INDEX function & N(IF(1,Array) Trick

Excel Magic Trick 0:06:36

Excel Magic Trick 1302: List Text Modes in Single Cell with TEXTJOIN, HLOOKUP, MATCH & MODE.MULT

Excel Magic Trick 0:09:00

Excel Magic Trick 1307: Conditionally Format Multiple Non-Adjacent Cells in Cross Tabulated Table

Excel Magic Trick 0:03:23

Excel Magic Trick 1310: Minimum Value Greater Than Zero with: 1) SMALL & COUNTIFS, 2) LARGE?

Excel Magic Trick 0:06:14

Excel Magic Trick 1304: List Text Modes in Single Cell TEXTJOIN, INDEX, N, IF, MATCH & MODE.MULT

Excel Magic Trick 0:09:42

Excel Magic Trick 1309: Extract Unique List and List in Single Cell: TEXTJOIN Array Formula

Excel Magic Trick 0:02:26

Excel Magic Trick 1308: Concatenate Items in Rows & Columns from Two-Way Table with TEXTJOIN

Excel Magic Trick 0:06:24

Excel Magic Trick 1293: Single Cell 'Lookup Adding' w Proper Data Set for Myers-Briggs Tes...

Excel Magic Trick 0:13:06

Excel Magic Trick 1118: MODE for Text Values, Extract Word That Occurs Most Frequently

Excel Magic Trick 0:07:10

Excel Magic Trick 1294: Single Cell 'Lookup Adding' w Improper Data Set to Score Myers-Bri...

Excel Magic Trick 0:07:11

Excel Magic Trick 1311: Return Multiple Items from One Lookup Value & Show Total for Invoice Ite...

Excel Magic Trick 0:06:04

Excel Magic Trick 1303: Minimum Value Greater Than Zero (3 Examples)

Excel Magic Trick 0:08:25

Excel Magic Trick 1312: Count The Number Of Mondays & Tuesdays Since Birth: NETWORKDAYS.INTL

Mini RC Helicopter 0:00:21

Mini RC Helicopter VS Big Remote Control Helicopter

Excel Magic Trick 0:03:54

Excel Magic Trick 1399: Join All Dates Together in a Cell using TEXTJOIN & TEXT Array Formula

Excel Dynamic Arrays: 0:17:03

Excel Dynamic Arrays: FILTER Array Function AND & OR Logical Tests (Excel Magic Trick 1529)

Running Total in 0:31:16

Running Total in Column, Power Query, DAX or Worksheet Formula? Excel Magic Trick 1553

1) Excel Data 0:31:36

1) Excel Data Navigation with Tips and Tricks

Power Query: Lookup 0:11:44

Power Query: Lookup 3 Lowest Prices from 3 Criteria Lookup Table. Excel Magic Trick 1738.

Create a visualization 0:01:39

Create a visualization with excel data stored on your iOS device

0301 IF Syntax 0:07:56

0301 IF Syntax And Uses | EXCEL FULL COURSE | InfiniteSkills Advanced.Microsoft Excel

Комментарии
Автор

Thank you very much, for all the efforts made to build your great Excel library on this channel. Even for this Belgian Excel user (Dutch version Excel 2016) the instructions are very clear. Excellent!

josdiepenbeek
Автор

My goodness. Watching this makes me want to learn everything about excel and be a magician!

TwoLargePizzas
Автор

Wow, this is SO over my head but one of these days I'll get there.  I'm not giving up though and I'm working my way through all your other videos

sesagolu
Автор

my goddd mike: my head is and index to house the entire array to avoid the CSE! Clever! Great video

jazzista
Автор

Power Query Solution (Works if column has either numbers or text only & not both in single column)Step 1: Load the data in Power QueryStep 2: Right click where mode needs to found > Drill Down             This step would convert selected column into list.Step 3: Click on "fx" icon in formula bar and enter "=List.Modes(Step 2 name)"              Step 2 name can be taken from "Applied Steps" window             This will give list of all modes.Step 4: From List Tools tab click "To Table"Step 5: Close & Load.This is all dynamic and will give list of modes in text or numbers. Will share the video link shortly.

AbhayGadiya
Автор

Woohoo! TEXTJOIN ARRAY FORMULA DIDNT SEE THAT COMMING!

levelzeroD
Автор

Awesomely nice video. Keep going, sir.

sim
Автор

We could have used also =TEXTJOIN(", ", 1, SORT(MODE.MULT(A2:A25)))

salasartravels
Автор

video : u apply sorted modes function....then where is the <=60 values... in D5 cell.... ? example : 20, 21, 29,

sri
Автор

We could have used also =TEXTJOIN(", ", 1, SORT(MODE.MULT(A2:A25)))

punitagarwal