Sort numbers among 2 columns, Eliminate Duplicates, Results in 3rd column

[westend9876]

Here is what I have in columns A and D respectively. I need to:

1.) sort both columns into on column from smallest to largest
2.) remove duplicates
3.) place results in Column F

Column A Column D
21............ 21
7 ..............23
22............ 14
17.............. 2
15 ............
22 ............21
22............
15.............. 1
9.............. 10
22 ............12
6 ................2

 

[bosco_yip]

Try,

In F2, formula copy down :

=IFERROR(SMALL(A$2:D$12,SUMPRODUCT((A$2:D$12>0)*(A$2:D$12<=F1))+1),"")

Regards
Bosco

 

[westend9876]

Try,

In F2, formula copy down :

=IFERROR(SMALL(A$2:D$12,SUMPRODUCT((A$2:D$12>0)*(A$2:D$12<=F1))+1),"")

Regards
Bosco

Thanks. The only issue with this formula is that there is data in columns B and C that should not be considered. Is there a modification that can be made to your formula so that the data in Columns A and D ONLY are considerded

 

[Chihiro]

Using concept found in link. @XOR LX 's site.
https://excelxor.com/2014/09/05/index-returning-an-array-of-values/#more-98

You can replace A$2:D$12 in bosco's formula with...
INDEX(A$2:D$12,N(IF(1,ROW(A$2:D$12)-MIN(ROW(A$2:D$12))+1)),N(IF(1,{1,4})))

So formula becomes...
=IFERROR(SMALL(INDEX(A$2:D$12,N(IF(1,ROW(A$2:D$12)-MIN(ROW(A$2:D$12))+1)),N(IF(1,{1,4}))),SUMPRODUCT((INDEX(A$2:D$12,N(IF(1,ROW(A$2:D$12)-MIN(ROW(A$2:D$12))+1)),N(IF(1,{1,4})))>0)*(INDEX(A$2:D$12,N(IF(1,ROW(A$2:D$12)-MIN(ROW(A$2:D$12))+1)),N(IF(1,{1,4})))<=F1))+1),"")

Confirmed as array (CSE)