Unique list in alphabetical order of 2 columns

[Berna11]

Good evening as the title suggests I would combine 2 columns A and D
and obtain a single unique list in alphabetical order.
Expected result in column H
Thank you

[alansidman]

Using Power Query. Load both tables to PQ, Append one to the other. Filter for nulls. Click on Remove Rows and select Remove Duplicates. Sort Ascending. Close and Load to Excel. See attached file to review.

[Berna11]

Grazie. Cercavo una formula, se possibile

[XOR LX]

Ciao,

First, go to Name Manager and make the following definitions:

Name: Range1
Refers to: =Foglio1!$A$2:$A$20

Name: Range2
Refers to: =Foglio1!$D$2:$D$20

(Or whatever happen to be the relevant ranges in question. Note, however, that Range1 and Range2 must comprise the same number of rows, as in your example.)

Name: Range_Join
Refers to: =CHOOSE({1,2},Range1,Range2)

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

Name: Row_Arry
Refers to: =1+INT((Arry1-1)/2)

Name: Col_Arry
Refers to: =1+MOD((Arry1-1),2)

Name: Main_Arry
Refers to: =INDEX(Range_Join,N(IF(1,Row_Arry)),N(IF(1,Col_Arry)))

After which, in H2, array formula**:

=IFERROR(INDEX(Main_Arry,MATCH(LARGE(IF(FREQUENCY(IF(Range_Join<>0,MATCH(Range_Join,Main_Arry,0)),Arry1),MMULT(0+(Main_Arry<=TRANSPOSE(Main_Arry)),Arry1^0)),ROWS(A$2:A2)),IF(FREQUENCY(IF(Range_Join<>0,MATCH(Range_Join,Main_Arry,0)),Arry1),MMULT(0+(Main_Arry<=TRANSPOSE(Main_Arry)),Arry1^0)),0)),"")

and copied down until you start to get blanks for the results.

Notes:

1) The above set-up assumes that zero is not a valid return within the ranges specified.
2) If you are using an Italian language version of Excel then, as well as translating all formulas, note that the separator within the array constant

=CHOOSE({1,2},Range1,Range2)

may well need amending, i.e. from a comma to (I believe) a backslash:

=SCEGLI({1\2};Range1;Range2)

Saluti


**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).

[kvsrinivasamurthy]

ARRAY formula in F2 then drag down till blank row appears. Pl see file.

Please Login or Register  to view this content.

[Berna11]

Sorry for the delay I had the computer broken.
Thank you all for your help