Results 1 to 4 of 4

Thread: order 2 non-adjacent columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default order 2 non-adjacent columns

    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

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: order 2 non-adjacent columns

    Try this for results in column "H" :-
    Code:
    Sub MG01Sep13
    Dim Rng As Range, Dn As Range, R As Range, Ray As Variant
    Set Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set R = Union(Rng, Rng.Offset(, 3))
        With CreateObject("System.Collections.ArrayList")
            For Each Dn In R
               If Not .contains(Dn.Value) And Dn.Value <> "" Then
                    .Add Dn.Value
                End If
            Next
            .Sort
            Ray = .ToArray
        End With
    Range("H2").Resize(UBound(Ray, 1) + 1).Value = Application.Transpose(Ray)
    Regards Mick

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: order 2 non-adjacent columns

    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)

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,963
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: order 2 non-adjacent columns

    Cross posted https://www.excelforum.com/excel-for...t-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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •