Unique entries formula from 2 lists

[pauldaddyadams]

Hi,

I have two lists and I would like a formula which only returns unique values – similar to an advanced formula. Is this possible?

In the attached example my data is in columns C and D and I would like a formula in column E if possible. I need it to ignore the capitals. E.g. BLACKBURN and Blackburn appears in each list but I would only want one instance pulled through in column E.

Currently in column E I have the desired result.

[XOR LX]

Hi,

And you're also wanting the returned list to be in alphabetical order?

Regards

[pauldaddyadams]

Hi,

No this wouldnt have to be, just a nice to have

[XOR LX]

This set-up will return a unique, alphabetically-sorted list from any number of columns, not just two.

First go to Name Manager (Formulas tab) and define the following:

Name: Range1
Refers to: =$C$61:$D$75

(Or whatever happens to be the range in question. You can err on a larger range to account for future additions to that range if you want, e.g. $C$61:$D$150: the formula is designed to ignore blanks. However, since this set-up uses array formulas, I would strongly recommend not making it too large (and certainly not the entire column).)

Name: Arry1
Refers to: =ROW(INDIRECT("1:"&COLUMNS(Range1)*ROWS(Range1)))

Name: Arry2
Refers to: =INT((Arry1-1)/COLUMNS(Range1))

Name: Arry3
Refers to: =MOD(Arry1-1,COLUMNS(Range1))

Exit Name Manager.

Enter this (non-array) formula in a cell somewhere, e.g. E59:

=SUMPRODUCT((Range1<>"")/COUNTIF(Range1,Range1&""))

Then enter this array formula** in your first cell of choice (e.g. E61):

=IF(ROWS($1:1)>$E$59,"",PROPER(INDEX(T(OFFSET(INDEX(Range1,1,1),Arry2,Arry3,,)),MATCH(SMALL(IFERROR(IF(FREQUENCY(IF(Range1<>"",MATCH(Range1,T(OFFSET(INDEX(Range1,1,1),Arry2,Arry3,,)),0)),Arry1),COUNTIF(Range1,"<"&T(OFFSET(INDEX(Range1,1,1),Arry2,Arry3,,)))+Arry1/10^9,""),""),ROWS($1:1)),COUNTIF(Range1,"<"&T(OFFSET(INDEX(Range1,1,1),Arry2,Arry3,,)))+Arry1/10^9,0))))

Copy down until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[TheCman81]

The attached arry formula will work for your example. As this is an array formula you need to enter it with Ctrl Shift Ent (Not sorted alphabetically)

Extract Unique values from multiple columns v2.xlsx

[pauldaddyadams]

Thanks all - sorry I have not replied till now! I thought I had