Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array Formula)

See how to:
1)Extract a unique list of records with a formula and display in a column using the functions IF, ROWS, INDEX, SMALL, FREQUENCY, MATCH, ROW, SUMPRODUCT and COUNTIF.
2)Extract a unique list of numbers only with a formula and display in a column with the extra function ISNUMBER.
3)Extract a unique list of words only with a formula and display in a column with the extra function ISTEXT.
4)Extract a unique list of records with a formula and display in a row (requires the additional function TRANSPOSE to deal with the fact that the FREQUENCY function delivers a vertical array and we need to have it orientated horizontally (in a row).

Also see this related videos:
Excel Magic Trick 362: 12 Amazing Counting Formulas
Excel Magic Trick 368: Count Unique Items With More Than One Criteria
Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula
Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula
Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula
Highline Excel Class 36: Array Formulas Basics

Aladin from Mr Excel Message Board

Category:

Science & Technology

Tags:

• Excel
• Extract
• Unique
• Records
• Array
• Formula
• FREQUENCY
• TRANSPOSE
• INDEX
• IF
• function
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Aladin
• from
• Mr
• Message
• Board

[xmcMaEc]

Thanks for the vid!

can you please explain more on why does the Sumproduct function needs to have a double negative symbol before the array?

I found this formula somewhere and I dont understand how it works, can you explain make a video with an example of this?

=+SUMPRODUCT(--(INDIRECT("Sche­dules!$C$5"):INDIRECT("Schedul­es!$C$2500")='SALES STAFFING'!$A$56),--(INDIRECT("­Schedules!$AN$5"):INDIRECT("Sc­hedules!$AN$2500")='SALES STAFFING'!$A331))

Thanks in advance!

[ExcelIsFun]

To learn about double negative, these two video titles explain:

Excel Formula Efficiency 1: SUMPRODUCT function 12 Examples

Highline Excel Class 37: SUMPRODUCT function

[kkontiki]

Excel is a lovely tool, though it needs deep thought to make it obey you. L0L

How much would a good excel programmer make?

[ExcelIsFun]

I don't know, I am not a good programmer; I am just a guy having fun with excel.

However, Employers are dry sponges that soak up and retain people that know Excel. This is because all of the working world is required to know Excel well, but the majority of working people do not know Excel.

If you have a job and you know Excel well, that is layoff insurance.

If you don't have a job and you are applying for a new position, if you know Excel well, they want to hire you.

excelisfun!

[krn14242]

Wow, that's some formula. I thought basic extracts were long. Thanks for the in-depth but easy explaination. Your an excellent teacher.

[ExcelIsFun]

You are welcome for the video! Thanks for the "EXCELlent" compliment.

[jacaetano]

Thanks for the video, it is great, and has been truly helpful.

But, is there any reason, although most of the columns seems right, that some columns the result from the sumproduct is #value, and in other columns, it appears blank instead of the unique value ?

[ExcelIsFun]

Wow! I love that analogy:

This is like learning how to drive, at first you have to pay attention to the breaks, to the clutch, the signals on the road, etc.

But then later that stuff is automatic and you can pay attention to the curves in the road ahead of you!

[chinchonchinchon]

I tried to do this formula by myself, and I did something similar. I got lost cause I didn't think of small nor Index, but hey, this is a step by step process. It is easy to get lot when there are many variables at play, meaning many different functions inside other functions.

This is like learning how to drive, at first you have to pay attention to the breaks, to the clutch, the signals on the road, etc.

[ExcelIsFun]

I am glad that they are useful!

[rehd]

Very good formula and tricks, love this video. Incredibly useful.