Dynamically extract unique values with no conditions

{ 4 Comments }

By Ashish Mathur · · , , · Leave a comment

Given a single column with duplicates appearing, one may want to dynamically extract unique values to another range.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system’s performance.

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Hi All,

    I have two list like below. need to find out :-

    1) Find unique from two lists without blank cells in between.
    2) Find common values without blank cells in between.

    List1 List2
    a a

    c c
    d
    e
    b b
    g
    h h
    d
    b

    Rajender

    Reply

  • Thanks a lot Ashish, its now working fine.
    Could you please assist to explain what is the role of small & row function here ?
    because little bit confused to understand the logic for the same.

    Thanks
    Rajender

    Reply

    • Hi,

      You are welcome. Glad to help. The SMALL() function return the first, second , third etc. smallest values from an array. The row function creates a series of consecutive numbers starting with 1. The IF() and MATCH() functions generate the position in the array from where values should be extracted. The SMALL() function then extracts the values from each of these positions.

      Hope this helps.

      Reply