Results 1 to 14 of 14

20081012, 04:29 #1
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 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

20081012, 05:13 #2
 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

20081012, 05:26 #3
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 Posts
Re: Formula to Extract Unique Data Values (2007)
Hi Steve
Any version would be fine
Regards
Howard

20081012, 07:34 #4
 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 ctrlshiftenter. 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 dblspace 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

20081015, 16:12 #5
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 Posts
Re: Formula to Extract Unique Data Values (2007)
Hi Steve
Thanks for the help
Regards
Howard

20081016, 14:33 #6
 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

20081017, 08:57 #7
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 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

20081019, 13:29 #8
 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

20081019, 16:29 #9
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 Posts
Re: Formula to Extract Unique Data Values (2007)
Hi Bosco
Thanks for the help. What does ROC??? mean in the formula
Regards
Howard

20081019, 18:34 #10
 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

20081019, 18:40 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20081020, 04:26 #12
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 Posts
Re: Formula to Extract Unique Data Values (2007)
Hi Bosco
Thanks or the explanationits much clearer now
Regards
Howard

20081020, 13:51 #13
 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

20081021, 04:49 #14
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 Posts
Re: Formula to Extract Unique Data Values (2007)
Hi Bosco
Thanks for the info
Regards
Howard