Extract unique values from 2 columns DYNAMICALLY

Hey!

I have two table columns which I need to merge the data from. Duplicates must be removed and the new list must be able to dynamically update itself when new entries are made.

I know this can be achieved with simple copy/paste & remove duplicates or with Advanced Filter -> Copy unique values, but these require a macro to be hassle-free.

Is there a formula I can use the extract the unique values dynamically?

Test.xlsx

This site explains how you can extract a list of uniques from two lists:

http://www.get-digital-help.com/2009...array-formula/

Define List1 and List2 as dynamic ranges if you will be adding data to them frequently.

Hope this helps.

Pete

@Pete_UK

Thanks a lot for the link Pete. I was looking into something similar, but it's a bit hard for me to decipher what's going on, because I am not familiar with most of these functions.

@oeldere

Thank you for taking the time to respond with a viable solution. The problem is I have to avoid using macros.

@dilipandey

Just when I gave up trying to do this with a PivotTable, you did it! What you have managed to achieve is precisely what I have been trying to do for days with the Wizard but to no avail.

Can you please explain what principles you use when you consolidate multiple ranges for PivotTable display? Can you use this same method for merging table columns?

Thanks a lot!

Hi Albatr0n,

Originally Posted by albatr0n

Thanks a lot for the link Pete. I was looking into something similar, but it's a bit hard for me to decipher what's going on, because I am not familiar with most of these functions.

The site does list the four functions that the formula uses, but in case you need a formula-based solution I have set this up in the attached file. I've created two dynamic named ranges called To and From and then put this array* formula in D2:

=IFERROR(IFERROR(INDEX(To, MATCH(0, COUNTIF($D$1:D1, To), 0)), INDEX(From, MATCH(0, COUNTIF($D$1:D1, From), 0))), "")

and then copied it down to D15 - copy it further if you need to.

As you enter new names in either the To or From columns, column D will give a list of the unique names in both lists.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter instead of the usual Enter.

Hope this helps.

Pete

It is possible to sort by formulae, but it would need to be done in a separate column (you could hide the column with the unique list in it, so that it would look the same).

As for a function like UNIQUE, well it is possible to write user-defined functions in Excel, so that array formula that I gave you could possibly be converted into such a function.

Hope this helps.

Pete

Example of a user defined function like Pete suggests.

Thanks for chipping in, guys. The problem is I have to avoid using macros in my sheets; that's why I always look for alternatives first.

I found this formula which pretty much nails it. If it could be simplified and integrated in Excel for quick input, it would be awesome!

That's another one from Oscar's site. Are you saying that you don't know how to apply it to your file?

Pete

A user-defined function (UDF) is written in VBA, and thus is a type of macro - but you've said that you want to avoid macros !!

I'm not sure why you would need to tweak the ranges in that formula once it is set up. If it refers to an Excel Table (which automatically adjusts to new entries, rather like my dynamic ranges earlier), then it should be okay. Also, if you put IFERROR( .... ,"") around it then you can copy it down much further than you would need and it would still function properly.

Hope this helps.

Pete