Excel Magic Trick #187: Unique List w Dynamic Formula

Create a unique list of items for a large list that contains duplicates with 2 array formula that contain the functions: SUM, IF, ROWS, COUNTIF, INDEX, SMALL, and ROW

Related Videos:
Excel Magic Trick #180: COUNTIF function formula 21 Examples
05 Excel Series: Array Formulas 1-14
Various INDEX Lookup w TRUE/FALSE criteria "Reverse Lookup"
Excel Magic Trick 185 Dynamic Formula Extract Data 1 criteria
Excel Magic Trick 186 Dynamic Formula Extract Data 2 criteria
Excel Magic Trick # 156: List Of Unique Items w Formula From 2 Lists
Excel Magic Trick 82: Advanced Filter Extract Unique Records

Category:

Science & Technology

Tags:

• Excel
• Unique
• List
• array
• formula
• SUMPRODUCT
• IF
• ROWS
• COUNTIF
• INDEX
• SMALL
• ROW
• functions
• Mike
• Gel
• Girvin
• Highline
• Community

[varunchh]

I am not able to extract UNIQUE RECORD, not sure what is going wrong. I followed your formula but except first value for remaining value it is giving "#NUM". Can you please help me. 

[ExcelIsFun]

It is too hard to have a dialog about an Excel solution with YouTube comments. Try post a question at THE best Excel question site:

mrexcel[dot]com/forum

BUT be sure to provide a clear question, say how your data is set up, and what your expected result is.

After you post, send me the link and I can take a look, however, there are 1000s of other people that may provide an answer before me.

[ExcelIsFun]

You can also send workbook example to me at: excelisfun at gmail.

[Pelonetillo]

i like the countif trick for the unique numbers, but is there a simple way to populate the unique records cell without relying on another cell to distinguish the first instance with a 1? also if possible to count unique only if another column has a "yes" for example. thanks!

[ExcelIsFun]

I have a very long comprehensive video about Unique value formulas using the FREQUENCY. It should answer both of your questions:

Excel Magic Trick 627: FREQUENCY Array Function (10 Examples)

also check this video:

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

[Markblackburn86]

This is great! thank-you. Do you have a video that shows how to do this with formulas without using the column "c"? so the end result would be 2 lists 1=master 2=unique of Master. I look forward to your reply, thanks again.

[ExcelIsFun]

I am in an untenable situation where I am working 80+ hours a week at work and am unable to answer questions currently. Sorry. Try this site for Excel questions:

Mrexcel[dot]com/forum

[DiscoverArts]

I know the best course of magic

[ExcelIsFun]

You are welcome!

[6642633]

This very helpful...

Thanks