order 2 non-adjacent columns

[Mattia]

Goodmorning everyone

In the attached file the data of our interest are in column A and column D where there are data, even repeated and empty cells.
The aim is to obtain a unique and alphabetically ordered list of the two columns without the use of service columns.
Expected results are in column H.
Thanks for your attention


https://www.dropbox.com/s/2r7fpmm8k990fv4/ordina%20due%20colonne.xlsx?dl=0

 

[MickG]

Try this for results in column "H" :-

[COLOR="Navy"]Sub[/COLOR] MG01Sep13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] R = Union(Rng, Rng.Offset(, 3))
    [COLOR="Navy"]With[/COLOR] CreateObject("System.Collections.ArrayList")
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] R
           [COLOR="Navy"]If[/COLOR] Not .contains(Dn.Value) And Dn.Value <> "" [COLOR="Navy"]Then[/COLOR]
                .Add Dn.Value
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR]
        .Sort
        Ray = .ToArray
    [COLOR="Navy"]End[/COLOR] With
Range("H2").Resize(UBound(Ray, 1) + 1).Value = Application.Transpose(Ray)

Regards Mick

 

[Mattia]

Buongiorno
grazie per l'attenzione ho sicuramente sbagliato a non menzionare che sto cercando una formula perché l'ho chiesto per puro spirito didattico e cioè trovare una formula senza colonne di servizio che faccia questo lavoro (con le colonne di servizio che posso raggiungere facilmente l 'obiettivo)

 

[Fluff]

Cross posted https://www.excelforum.com/excel-formulas-and-functions/1288288-order-2-non-adjacent-columns.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Also as this is an English language board, can you please keep all posts in English.
Thanks