Excel - UNIQUE of Rectangular Range Using MAKEARRAY function in Excel - Episode 2416
Microsoft Excel Tutorial: Using the MAKEARRAY function in Excel.
There is a newer video 2475 showing how to solve this with TOCOL: • Excel UNIQUE of a Rectangular Range Using ...
Welcome to episode 2416 of the MrExcel Podcast, where we dive into the world of Excel and explore its latest features and functions. In today's episode, we will be discussing the MAKEARRAY function and how it can be used to find the unique values in a rectangular range. This new function is a game-changer and will save you from the nine minutes and 48 seconds of frustration that comes with using traditional methods.
If you've been following our previous episodes, you would have learned about the various Lambda helper functions such as MAP, REDUCE, SCAN, BYROW, and BYCOL. Today, we will be focusing on MAKEARRAY, which is a simple yet powerful function. All you need to do is specify the number of rows and columns you want and the logic or formula you want to apply to each intersection of row and column.
One of the most common uses of MAKEARRAY is to create a multiplication table, where the formula is R times C. However, it can be used for any calculation, such as two times R divided by C. But the real magic of MAKEARRAY is seen when we use it to find the unique values in a rectangular range. In the past, this was a tedious task that involved multiple methods and formulas. But with MAKEARRAY, it's just a matter of a few simple steps.
In this episode, we will be using a rectangular range of data and finding the unique values in it. This is not a "by row" basis, but rather a single column of unique values. To achieve this, we will be using the LET function to assign the array, the ROWS and COLUMNS functions to count the number of rows and columns, and the MAKEARRAY function to create an array that is one column wide and 84 rows tall. The calculation will be a Lambda function that passes R and C, with C being constant at 1. This may seem complex, but it's a simple dance of using the MOD function and rounding up to get the right column.
Once we have the unique values in a single column, it's just a matter of wrapping the formula in the UNIQUE function to get a sorted list of unique values. And if you want to take it a step further, you can add the SORT function to get a sorted unique list. This method may not be any better than the traditional methods, but it's definitely a lot shorter and more efficient. So why not give it a try and see for yourself?
If you enjoyed this episode, don't forget to hit the like button, subscribe to our channel, and ring the bell to get notified of our latest videos. And as always, feel free to leave your questions and comments down below. Thank you for tuning in, and we'll see you in the next episode of the MrExcel Podcast.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Another new LAMBDA helper function in Excel is MAKEARRAY. This video shows a simple example, and then tackles the problem from episode 2317 of how to unwind a rectangular range into a single column so you can pass it to UNIQUE and SORT.
The formula used in the video is:
=LET(array,B2:G15,
rowcount,ROWS(array),
colcount,COLUMNS(array),
itemcount,rowcount*colcount,
MAKEARRAY(itemcount,1,
LAMBDA(r,c,
INDEX(array,
LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)),
ROUNDUP(r/rowcount,0))
)))
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
This video answers these common search terms:
excel how to extract unique
how do i find unique values in excel?
how do you use unique in excel?
how to consolidate unique values in excel
how to create a unique values list in excel?
how to extract a list of uniques values in excel
how to extract all unique values in excel
how to extract unique values from range in excel
how to find unique values in a range in excel
how to pull unique values between two excel columns
how to show unique values in excel
how to use the unique function in excel
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#excelformula
#excelformulasandfunctions
Table of Contents
(0:00) MAKEARRAY function in Excel
(0:38) Simple use of MAKEARRAY
(1:08) UNIQUE of a rectangular range of data
(1:30) LET function in Excel with MAKEARRAY
(2:49) Using UNIQUE function around formula
(3:15) Wrap-up
Auf dieser Seite können Sie das Online-Video Excel - UNIQUE of Rectangular Range Using MAKEARRAY function in Excel - Episode 2416 mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer MrExcel.com 31 Juli 2021 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 7,503 Mal angesehen und es wurde von 165 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
