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

[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 ?

[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!

[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]

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!

[rehd]

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

[ExcelIsFun]

I am glad that they are useful!

[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.

[planiolro]

indeed it is a long and complicated formula but useful one.

[ExcelIsFun]

Yes, and the FREQUENCY function is the most versatile function that I know of for determining "unique" counts / extracts and other topics. I am just starting to appreciate how useful it is after studying the Mr Excel Message Board post from Aladin that I referred to in the video.