+ Reply to Thread
Results 1 to 6 of 6

How to extract unique data from two columns

Hybrid View

  1. #1
    Registered User
    Join Date
    12-29-2011
    Location
    ShangHaii, China
    MS-Off Ver
    Excel 365, 2016
    Posts
    32

    How to extract unique data from two columns

    My spreadsheet has two columns : Name list and Sezi, please help me to extract unique data from this two columns and display result like attachment file.

    Thanks in advanced!
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to extract unique data from two columns

    Please see attached file with formulas
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    12-29-2011
    Location
    ShangHaii, China
    MS-Off Ver
    Excel 365, 2016
    Posts
    32

    Re: How to extract unique data from two columns

    Sorry Alkey! I want to write code in this case, my data has about 5000 rows. I want to use Dictionary Object in this case but I cant beacuse I dont know to use Dictionary Object.
    Your formula not display the result that I desire, with name list is AA in column F, sezi is : 0333, 0444M, 0022 but your result is : 0333, 0333, 0444M, 0022, hix
    Who can hepl me, please?

  4. #4
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,449

    Re: How to extract unique data from two columns

    see attachment
    Attached Files Attached Files

  5. #5
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: How to extract unique data from two columns

    Try this:-
    Results start "F1"
    Sub MG22Dec31
    Dim Rng     As Range
    Dim n       As Long
    Dim Dn      As Range
    Dim Dic     As Object
    Dim oDup    As Variant
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
            Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
                With CreateObject("scripting.dictionary")
                    .CompareMode = vbTextCompare
    For Each Dn In Rng
        If Not Dn = vbNullString Then
            If Not .exists(Dn.Value) Then
                .Add Dn.Value, Format(Dn.Offset(, 1), "@")
        Else
            .Item(Dn.Value) = .Item(Dn.Value) & ", " & Dn.Offset(, 1)
            oDup = Split(.Item(Dn.Value), ", ")
                For n = 0 To UBound(oDup)
                    Dic(oDup(n)) = Empty
                Next n
            .Item(Dn.Value) = Join(Dic.keys, ", ")
            Dic.RemoveAll
        End If
    End If
    Next
    Range("F1").Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
    Range("G1").Resize(.Count).NumberFormat = "@"
    End With
    
    End Sub
    Regards Mick

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How to extract unique data from two columns

    The output is in sheet2.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find unique field in one column and extract data in previous columns
    By Waqaskp in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-18-2013, 01:01 AM
  2. Replies: 8
    Last Post: 09-12-2013, 09:17 AM
  3. Extract unique data from rows to columns
    By CherryBlossom in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2013, 05:12 AM
  4. [SOLVED] Extract unique values from 2 columns DYNAMICALLY
    By albatr0n in forum Excel General
    Replies: 17
    Last Post: 08-28-2012, 08:42 AM
  5. extract Unique Records from multipe columns
    By carsto in forum Excel General
    Replies: 4
    Last Post: 11-20-2007, 10:10 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1