Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula to Extract Unique Data Values (2007)

    I have three lists of data One in Col A, another in Col B and the third one in Col C. I would like a formula to extract all the unique data so that the appear in Col D

    See Attached sample

    Your assistance will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to Extract Unique Data Values (2007)

    Does it have to be an XL2007 solution or is the dataset unique to XL2007?

    If not could you save a copy as an earlier version for those of us without XL2007 (and either using versions where the converter does not work or is not allowed to do since a work computer does not grant admin access to install programs)?

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Extract Unique Data Values (2007)

    Hi Steve

    Any version would be fine

    Regards

    Howard

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to Extract Unique Data Values (2007)

    You can add this code to module:

    <pre>Option Explicit
    Function UniqueList(rng As Range) As Variant
    Dim sNames() As String
    Dim rCell As Range
    Dim colUnique As New Collection
    Dim x As Integer
    Dim y As Integer
    Dim sTemp As String
    Dim sTemp2 As String
    Dim AWF As WorksheetFunction

    Set AWF = Application.WorksheetFunction
    ReDim sNames(1 To rng.Count)
    For x = 1 To rng.Count
    sNames(x) = ""
    Next
    On Error Resume Next
    For Each rCell In rng
    colUnique.Add AWF.Trim(rCell.Value), CStr(AWF.Trim(rCell.Value))
    Next
    On Error GoTo 0

    For x = 1 To colUnique.Count - 1
    For y = x + 1 To colUnique.Count
    If colUnique(x) > colUnique(y) Then
    sTemp = colUnique(x)
    sTemp2 = colUnique(y)
    colUnique.Add sTemp, before:=y
    colUnique.Add sTemp2, before:=x
    colUnique.Remove x + 1
    colUnique.Remove y + 1
    End If
    Next
    Next

    For x = 1 To colUnique.Count
    sNames(x) = colUnique(x)
    Next
    UniqueList = AWF.Transpose(sNames)
    Set rCell = Nothing
    Set rng = Nothing
    Set AWF = Nothing
    End Function</pre>


    Then you can select D37 (or an appropriately large range) and enter:
    =uniquelist(A3:C5)
    and confirm with ctrl-shift-enter. The names will be sorted. [Note I added trimming the text string or else some of the names were not unique. One of the Peter Lyons has a dbl-space between first and last names and one of he Howard Davids has several trailing spaces]

    If the range you select is more than the number of cells in the selected range then those cells will have a #N/A error. cells will have the null if they are for > than the number of unique names but < still within the rangecount. If your range is not large enough you will only get the number of unique items in the range defined by the formula.

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Extract Unique Data Values (2007)

    Hi Steve

    Thanks for the help

    Regards

    Howard

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to Extract Unique Data Values (2007)

    The OP wanted a formula to extract all the unique data

    A quite Challenge to have a formula way in remove duplicate at 3 columns datas

    Please enter the array formula at D2 and copy down

    {=IF(OR((A$2:C$10<>"")*(COUNTIF(D$11,A$2:C$10)=0)),INDIRECT(TEXT(MIN(IF((A$2:C$10<>"" )*(COUNTIF(D$11,A$2:C$10)=0),ROW(A$2:C$10)*1000+COLUMN(A:C))),"R 0C???"),),"")}

    Regards
    Bosco
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Extract Unique Data Values (2007)

    Hi Bosco

    Thanks for the help. Quite a complex formula that you have set up

    Regards

    Howard

  8. #8
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to Extract Unique Data Values (2007)

    The formula can shorten to :

    {=INDIRECT(TEXT(MIN(IF((A$2:C$10<>"")*COUNTIF(D$11,A$2:C$10),6000,ROW($2:$10)*1000+{1,2,3})),"R0C?? ?"),)&""}

    Regards
    Bosco

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Extract Unique Data Values (2007)

    Hi Bosco

    Thanks for the help. What does ROC??? mean in the formula

    Regards

    Howard

  10. #10
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to Extract Unique Data Values (2007)

    R0C??? <--- R1C1 reference style

    Excel indicates the location of a cell with an "R" followed by a row number

    and a "C" followed by a column number

    R1C1 <--- A1

    R2C1 <--- A2

    Example :

    In Cell D2, the formula is :

    {=INDIRECT(TEXT(MIN(IF((A$2:C$10<>"")*COUNTIF(D$11,A$2:C$10),6000,ROW($2:$10)*1000+{1,2,3})),"R0C?? ?"),)}

    =INDIRECT(TEXT(MIN({2001,2002,2003;3001,3002,3003; 4001,4002,4003;5001,5002,5003;6001,6002,6003;7001, 7002,7003;8001,8002,8003;9001,9002,9003;10001,1000 2,10003}),"R0C???"),)

    =INDIRECT(TEXT(2001,"R0C???"),)

    =INDIRECT("R2C001",) <--- cell A2

    ="Howard Davids"

    Regards
    Bosco

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to Extract Unique Data Values (2007)

    It's not ROC??? (with a letter O) but R0C??? (with the digit zero). It's the format string for the TEXT function; it formats the first argument as an R1C1 style cell reference.

    Note: this also means that the formula may not work in other language versions of Excel without modification, since they might use other letters than R and/or C for Row and Column.

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Extract Unique Data Values (2007)

    Hi Bosco

    Thanks or the explanation-its much clearer now

    Regards

    Howard

  13. #13
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to Extract Unique Data Values (2007)

    {=INDIRECT(TEXT(MIN(IF((A$2:C$10<>"")*COUNTIF(D$11,A$2:C$10),6000,ROW($2:$10)*1000+{1,2,3})),"R0C?? ?"),)}

    Two more points of the formula to be cleared that

    1st point (column numbers of the data range) :

    {=..+{1,2,3})),"R0C???"),)}

    This part ...{1,2,3}.,<------ that mean the data ranges, were located at "Column A to Column C"

    If the data ranges were located at Column E to Column K

    then, the formula will be modified to

    ...{,5,6,7,8,9,10,11}.

    or, replaced by :

    ...COLUMN(E:K).

    2nd point ( error control )

    {=................IF(.........,6000,.........),... ......}

    This part ...6000.,<------ that mean, the result will return to A6000 (giving BLANK) by the IF function evaluates to TRUE



    Regards
    Bosco

  14. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Extract Unique Data Values (2007)

    Hi Bosco

    Thanks for the info

    Regards

    Howard

Posting Permissions

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